Types of Joins,SQL Server

Types of joins in SQL.

1. Inner Join
   
   
2. Outer Join
   
   
   • Left Outer Join
     
     
   • Right Outer Join
     
     
   • Full Outer Join
     
     
3. Cross Join

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.

1)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.

Examples:

SQL Inner Join query with (=) operator:

SELECT C.CATEGORYID, C.CATEGORYNAME, P.PRODUCTID, P.PRODUCTNAME, P.UNITPRICE
FROM CATEGORIES C INNER JOIN
PRODUCTS P ON P.CATEGORYID = C.CATEGORYID
WHERE P.UNITPRICE = 10
ORDER BY C.CATEGORYNAME, P.PRODUCTNAME

This inner join query will return the categoryid, categoryname, productid, productname, unitprice where product unit price = 10

SQL Inner Join Query with (>) operator:

SELECT DISTINCT C.CATEGORYID, C.CATEGORYNAME
FROM CATEGORIES C INNER JOIN
PRODUCTS P ON C.CATEGORYID > P.CATEGORYID
WHERE P.UNITPRICE = 10
ORDER BY C.CATEGORYNAME

This inner join query will return the categoryId, categoryName having products with unit price=10

SQL Inner Join Query with not equal (<>) operator:

SELECT DISTINCT P1.PRODUCTNAME, P1.UNITPRICE, P1.SUPPLIERID
FROM PRODUCTS P1 INNER JOIN PRODUCTS P2
ON
P1.SUPPLIERID=P2.SUPPLIERID
AND P1.UNITPRICE<>P2.UNITPRICE
WHERE P1.UNITPRICE <>
ORDER BY P1.SUPPLIERID

Inner Join with not equal operator is rarely used in self joins. As an example above sql self join query returns the productname, unitprice, supplierid where suppliers having 2 or more than 2 products with unit price less than 20.

2)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.
  
  • Inner joins return only those rows from both sql database tables having matching records in both the tables whereas left outer join returns all the rows from the left table and related matching records from the other one.

SQL Left Outer Join Example:

SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
FROM AUTHORS A LEFT OUTER JOIN PUBLISHERS P
ON A.CITY = P.CITY
ORDER BY A.AU_LNAME, A.AU_FNAME

• 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.
  
  • Just change the left keyword to right outer join in above example; you will get the reverse output of left outer join in the form of right outer join.

SQL Right Outer Join query Example:

SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
FROM AUTHORS A RIGHT OUTER JOIN PUBLISHERS P
ON A.CITY = P.CITY
ORDER 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.
  
  • To retrieve all the records from left as well as right table unless the records have matching relations in each row you can use SQL FULL OUTER JOIN.
    
    • You can consider the examples of last two articles about left outer join and right outer join, in which left outer join retrieves all records from the left table and as all records of right table in right outer join along with null values for the columns having no matching records in any tuple. To retain all the records of left as well as right table along with null values for non matching rows displaying the combination of results of left outer and right outer join, FULL OUTER JOIN is the best solution.

SQL FULL outer join example:

SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
FROM AUTHORS A FULL OUTER JOIN PUBLISHERS P
ON A.CITY = P.CITY
ORDER BY A.AU_LNAME, A.AU_FNAME

3)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.

• SQL Cross join returns the output result as a Cartesian product of both database tables.
  
  • Let left table has 10 rows and right table has 8 rows then SQL CROSS Join will return 180 rows combining each record of left table with all records of right side table. Consider the following example of CROSS Join:

USE PUBS
SELECT AU_FNAME, AU_LNAME, PUB_NAME
FROM AUTHORS CROSS JOIN PUBLISHERS
ORDER BY AU_FNAME

Above cross join will return 23 * 8 = 184 results by multiplying each row of authors table with publishers table.

SQL CROSS Join with WHERE clause

By just adding the where clause with Cross join sql query it turns the output result into inner join.

Example:

USE PUBS
SELECT AU_FNAME, AU_LNAME, PUB_NAME
FROM AUTHORS CROSS JOIN PUBLISHERS
WHERE AUTHORS.CITY = PUBLISHERS.CITY
ORDER BY AU_FNAME