SQL Commands (Part-3)

JOINS:

Inner Join: Gives you common result from two or more tables. Intersecting part of a Venn Diagram.

SELECT * from Person.Address — Count number of records

SELECT * from Person.StateProvince — Count number of records

Notice: The result set of first SELECT statement only has StateProvinceID but not StateName.

Select AddressLine1, City, PostalCode, B.Name as StateName from Person.Address as A
Inner Join Person.StateProvince as B
ON A.StateProvinceID = B.StateProvinceID  — Count number of records and Compare

Few rows are eliminated because not all records from Person.Address table has StateProvinceID.

Three Joins!

Select PC.Name as ProductCategory,
PS.Name as ProductSubCategory,
PRD.Name as ProductName, ProductNumber
FROM Production.Product as PRD
INNER JOIN Production.ProductSubcategory PS
ON PRD.ProductSubcategoryID=PS.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
ORDER BY PC.NAME, PS.NAME

Left Join: All matched records from Person.[Address] table + Person.[StateProvince]. No match will yield NULLS.

Select AddressLine1, City, PostalCode, B.Name as StateName from Person.Address as A
LEFT Join Person.StateProvince as B
ON A.StateProvinceID = B.StateProvinceID

Right Join: All matched records from Person.[StateProvince] table +Person.[Address] . No match will yield NULLS.

Select AddressLine1, City, PostalCode, B.Name as StateName from Person.Address as A
RIGHT Join Person.StateProvince as B
ON A.StateProvinceID = B.StateProvinceID

Full Outer Join: Will contain all records from both tables. Missing matches will yield NULLS

Select AddressLine1, City, PostalCode, B.Name as StateName from Person.Address as A
FULL OUTER Join Person.StateProvince as B
ON A.StateProvinceID = B.StateProvinceID

If you are retrieving two additional columns – A.StateProvinceID and B.StateProvienceID how will results differ in each join cases? If you can answer this, you are good for joins! Also, Full Join is not available in MySql.

Tip: If you want all the columns from A, you can use SELECT A.* from Person.[Address]. If you want all columns from A and B then A.*,B.* will do the trick. The following statements are same.

SELECT * from Person.Address

Select A.* from Person.Address as A
LEFT Join Person.StateProvince as B
ON A.StateProvinceID = B.StateProvinceID

Self Join: When you join the same table with itself.

SELECT *from Person.StateProvince A
INNER JOIN Person.StateProvince B
ON A.StateProvinceID=B.StateProvinceID

If you want to identify who the manger is for certain employee then you can use Self Join, provided everything is in the same table. Doing this prevents duplicate records.

Cross Join: Also called Cartesian product (each row from first table is combined with each row of second table)

Select City, PostalCode, B.Name as StateName, B.StateProvinceID from
Person.[Address] as A
CROSS JOIN Person.[StateProvince] as B

If you use Where clause in cross join, it acts like an inner join. Also note that our output will have more than almost 2 million records! Each record from Address table was cross joined with each record of StateProvince table.