JOINS

JOINS

--============================================[JOINS]========================================================================

--JOIN clause is used whenever we have to select data from 2 or more tables.

--Our two table are below---

SELECT * FROM Person.Contact [19972 rows]

Select * From Sales.Individual [18484 rows]

--Joining without using JOIN Clause (Only Matched)[18484 rows]---------

SELECT Person.Contact.FirstName, Person.Contact.LastName,Sales.Individual.CustomerID

FROM Person.Contact,Sales.Individual

WHERE Person.Contact.ContactID = Sales.Individual.ContactID

--JOIN (By default it will be INNER JOIN)(Only Matched)[18484 rows]

--The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on.

SELECT Person.Contact.FirstName, Person.Contact.LastName,Sales.Individual.CustomerID

FROM Person.Contact JOIN Sales.Individual

ON Person.Contact.ContactID = Sales.Individual.ContactID

--LEFT OUTER JOIN (simply LEFT JOIN) (19972 rows)---

-- selects all the rows from the first table listed after the FROM clause, no matter if

--they have matches in the second table.

SELECT Person.Contact.FirstName, Person.Contact.LastName,Sales.Individual.CustomerID

FROM Person.Contact LEFT OUTER JOIN Sales.Individual

ON Person.Contact.ContactID = Sales.Individual.ContactID

--RIGHT OUTER JOIN (simply RIGHT JOIN) (18484 rows)

-- selects all the rows from the second table listed after the FROM clause

SELECT Person.Contact.FirstName, Person.Contact.LastName,Sales.Individual.CustomerID

FROM Person.Contact RIGHT OUTER JOIN Sales.Individual

ON Person.Contact.ContactID = Sales.Individual.ContactID