SQL Server - Subquery

Tips:

---

All tasks of sub-query can be replaced with SQL server JOIN. So, it is recommend to use JOIN. But learn sub-query for understanding.

Careful:

---

• does not support ORDER BY
• You can’t use BETWEEN in the main query. But sub-query allows BETWEEN.
• sub-query is a filter. so, inside sub-query, you can’t use more than one column

SQL Server code:

---

-- find the personal details using the phone number

select BusinessEntityID

from   Person.PersonPhone

where  PhoneNumber='612-555-0100'

select FirstName, LastName from Person.Person

where BusinessEntityID in (4, 2115, 2167)

select FirstName, LastName

from Person.Person

where BusinessEntityID in (

               select BusinessEntityID

               from Person.PersonPhone

               where PhoneNumber='612-555-0100'

               )