SQL Server Joins

Joins:
Joins in SQL Server allows the retrieval of data records from one or more tables having some relation between them. Logical operators can also be used to drill down the number of records to get the desired output from sql join queries.
Types:

• Inner Join
• Outer Join 1)Left Outer Join 2)Right Outer Join 3)Full Outer Join
• Cross Join
• Inner Join: Inner Join is a default type join of SQL Server. It uses logical operators such as =, <, > to match the records in two tables. Inner Join includes equi join and natural joins.
• Example:
• SELECT C.CATEGORYID, C.CATEGORYNAME, P.PRODUCTID, P.PRODUCTNAME, P.UNITPRICE FROM CATEGORIES C INNER JOINPRODUCTS P ON P.CATEGORYID = C.CATEGORYIDWHERE P.UNITPRICE = 10ORDER BY C.CATEGORYNAME, P.PRODUCTNAME
• Outer Join:
• Outer Join has further 3 sub categories as left, right and full. Outer Join uses these category names as keywords that can be specified in the FROM clause.
  Left Outer Join: Left Outer Join returns all the rows from the table specified first in the Left Outer Join Clause. If in the left table any row has no matching record in the right side table then that row returns null column values for that particular tuple.
• Example:
  SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAMEFROM AUTHORS A LEFT OUTER JOIN PUBLISHERS PON A.CITY = P.CITYORDER BY A.AU_LNAME, A.AU_FNAME
  Cheryl
  Carson
  Algodata Infosystems
  Michel
  DeFrance
  NULL
  
  Right Outer Join:
• Right Outer Join is exactly the reverse method of Left Outer Join. It returns all the rows from right table and returns null values for the rows having no match in the left joined table.
  Example:
• SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAMEFROM AUTHORS A RIGHT OUTER JOIN PUBLISHERS PON A.CITY = P.CITYORDER BY A.AU_LNAME, A.AU_FNAME
  
  Full Outer Join: Full outer join returns all the rows from both left and right joined tables. If there is any match missing from the left table then it returns null column values for left side table and if there is any match missing from right table then it returns null value columns for the right side table.
  SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAMEFROM AUTHORS A FULL OUTER JOIN PUBLISHERS PON A.CITY = P.CITYORDER BY A.AU_LNAME, A.AU_FNAME
• Cross Join: Cross join works as a Cartesian product of rows for both left and right table. It combined each row of left table with all the rows of right table.
• Example:
  SELECT AU_FNAME, AU_LNAME, PUB_NAMEFROM AUTHORS CROSS JOIN PUBLISHERS WHERE AUTHORS.CITY = PUBLISHERS.CITYORDER BY AU_FNAME