SQL SERVER - JOINS - II

    

 The LEFT JOIN

Also known as LEFT OUTER JOIN, this is a particular case of the FULL JOIN. It brings all requested data from the table that appears to the left of the JOIN operator, plus the data from the right table which intersects with the first one. Below we have a Venn diagram illustrating the LEFT JOIN of two tables in below figure.

 Representing the LEFT JOIN

See the syntax below,

SELECT t1.key1, t1.field1 as Name, t1.key2 as T1Key, 
   t2.key2 as T2Key, t2.field1 as City
FROM Table1 t1
   LEFT JOIN Table2 t2 ON t1.key2 = t2.key2 ;

The result set of this statement will be:

key1	Name	T1Key	T2Key	City
3	Erik	1	1	New York
4	John	4	4	Paris
6	Mark	7	null	null
7	Peter	8	null	null
8	Harry	9	9	Madrid

The third and forth records (key1 equals to 6 and 7) show NULL values on the last fields because there is no information to be brought from the second table. This means we have a value in field key2 in Table1 with no correspondent value in Table2. We could have avoided this "data inconsistency" in case we had a foreign key on field key2 in Table1.

The RIGHT JOIN

Also known as RIGHT OUTER JOIN, this is another particular case of the FULL JOIN. It brings all requested data from the table that appears to the right of the JOIN operator, plus the data from the left table which intersects with the right one. The Venn diagram for the RIGHT JOIN of two tables is in Figure 5.

 Representing the RIGHT JOIN

As you can see, syntax is very similar.

SELECT t1.key1, t1.field1 as Name, t1.key2 as T1Key,
   t2.key2 as T2Key, t2.field1 as City
FROM Table1 t1
   RIGHT JOIN Table2 t2 ON t1.key2 = t2.key2 ;

The result set of this statement will be:

key1	Name	T1Key	T2Key	City
null	null	null	0	Bangalore
3	Erik	1	1	New York
null	null	null	2	Sao Paulo
4	John	4	4	Paris
null	null	null	5	London
null	null	null	6	Rome
8	Harry	9	9	Madrid

Observe now that records with key1 equal to 6 and 7 no longer appear in the result set. This is because they have no correspondent record in the right table. There are 4 records showing NULL values on the first fields, because they are not available in the left table.

The CROSS JOIN

A CROSS JOIN is in fact a Cartesian product. Using CROSS JOIN generates exactly the same output of calling two tables (separated by a comma) without any JOIN at all. This means we will get a huge result set, where each record of Table1 will be duplicated for each record in Table2. If Table1 has N1 records and Table2 has N2 records, the output will have N1 times N2 records.
I don't believe there is any way to represent this output in a Venn diagram. I guess it would be a three dimensional image. If this is really the case, the diagram would be more confusing than explanatory.

The syntax for a CROSS JOIN will be:

SELECT t1.key1, t1.field1 as Name, t1.key2 as T1Key, 
 t2.key2 as T2Key, t2.field1 as City
FROM Table1 t1
 CROSS JOIN Table2 t2 ;

As Table1 has 5 records and Table2 has another 7, the output for this query will have 35 records (5 x 7).


Quite honestly, I don't remember at this very moment not a single real-life situation that I do need to generate a Cartesian product of two tables. But whenever you need, CROSS JOIN is there, anyway.
Besides, you should be concerned about performance. Say you accidentally run in your production server a query with a CROSS JOIN over two tables with 1 million records. This is surely something that will give you a headache. Probably your server will start showing performance problems, as your query might run for some time consuming a considerable amount of the server resources.


 In the next article, we will see other joins.
      

 Source: SQL Server Central