Joins & SELECT Sequence

Why do we need Joins?

Joins are used to retrieve data from two or more tables, based on a relationship between certain columns in these tables.

Types of Joins:

• Inner Join
• Left Outer Join
• Right Outer Join
• Full Outer Join 
• Cross Join
• Self Join

Inner Join 

Returns only matching rows or records in two or more tables. Below Venn diagram explains this.

Left Outer Join

Returns all the record from left table with matching record in right table. If there are no matching columns in right table then it returns NULL values.

Note : If left table has 5 records and right table has 7 records. Among these if matching records between left and right table are only 3 records then this join will return all 5 records from the left table and in right table also returns 5 records except that it will return 3 matching records and other two records will be NULL. Right table will not return other two non NULL and non matching records.

Right Outer Join

Returns all the records from the right table with matching records in left table. If there are no matching columns in left table then it returns NULL value.

Note : If left table has 5 records and right table has 7 records. Among these if matching records between left and right table are only 3 records then this join will return all 7 records from the right table and in left table also returns 7 records except that it will return 3 matching records and other 4 records will be NULL. left table will not return other 2 non NULL and non matching records.

Full Outer Join

This is the combination of Left Join and Right Join. Returns matching and non matching records from both the tables. For non matching record it will return NULL value.

Cross Join

Returns the Cartesian product i.e., each record in one table is joined with each and every record in other table. If Table1 has 3 records and Table2 has 2 records then it will return 3x2=6 records.

Self Join

This is a special type of join which may be Inner Join, Left Outer Join, Right Outer Join, where a Table joined on its own. i.e., a Table joined on itself. Only one table involved in this join.

Below is some extra details on Joins:

/********************  JOINS  ****************************/

/*

There are 2 types of joins

--> Physical joins

--> Logical joins

Physical Joins :

----------------

--> Nested loop join

--> Merge join

--> Hash join

-- These joins cannot be  performed by user where as SQL Server performes it internally.

-- Nested loop join will take a record from table1 and match with table2. For each record it does the same.

-- For Merge join the records in the tables should be sorted in some order. It will take a record from the 

   tables1 and search for value in table2, it will not search the records which is greater than the search value.

-- Nested loop works on lesser data where as merge join works on moderate amount of data.

-- Hash join is faster than other 2 joins.

Logical Joins :

----------------

--> Inner join

--> Equi join

--> Natural join

--> Outer join

--> Left outer join

--> Right outer join

--> Full outer join

--> Self join

--> Cross join / Cartesian join

--> Cross Apply

--> Outer Apply

--> Semi join

--> Anti semi join

-- Inner join is one which gives the exact matching records from the 2 tables.

-- According to microsoft Equi join and Natural join is as follows. 

   Equi join means we specify * in the SELECT statement and ON condition.

ex: SELECT * FROM Employee E INNER JOIN Contact C ON E.ContactID=C.ContactID

   Natural join means we specify column name in SELECT Statement.

ex: SELECT C.*,E.FirstName,E.LastName FROM Employee E INNER JOIN Contact C ON E.ContactID=C.ContactID

-- In oracle we can find Natural join key word but its not there in T-SQL. 

-- Left outer join is one where we get all the records in the left table and 

   only matching records in the right tables, rest non matching records in the right table 

   are returned as NULL.

-- Right outer join is one where we get all the records in the right table and only matching 

   records in the left tables, rest non matching records in the left table are returned as NULL.

-- Full outer join is one where first it does the left join and then the right join. Non matching 

   records in both the tables are left as NULL.

-- Self join is one where the table joins itself.

-- Cross join is one where each record in table1 joins with all the records in table2.

-- Cross Apply is like inner join and we can use functions with it. Whereas with inner join we cannot 

   use functions.

-- Outer Apply is like left outer join and we can use functions with it. Where as with left outer join we

   cannot use functions.

-- There is no specific keywords like Semi join and Anti Semi join , it is internally performed by SQL.

*/

Sequence of SELECT Statement :

SELECT [col1,col2,…,coln]

FROM [table/view]

WHERE [condition]

GROUP BY [Col1,col2,….]

HAVING [condition]

ORDER BY [col1,col2,….]

COMPUTE BY

FOR XML