TRAINING (SQL-7) - Data Query Language (DQL) - SQL Joins between two or more tables

SQL JOINS (How to Join Tables): 

• As soon as more then one table is mentioned in FROM clause of SQL, joins comes in picture.
• Usually JOINS will be performed between two tables based on the key column (Primary & Foreign key) between two or more tables.
• 1) CARTESIAN JOIN
• 2.) INNER JOIN
• 3.) LEFT JOIN
• 4.) RIGHT JOIN
• 5.) OUTER JOIN
• 6.) LEFT JOIN EXCLUDING INNER JOIN
• 7.) RIGHT JOIN EXCLUDING  INEER JOIN
• 8.) OUTER JOIN EXCLUDING INNER JOIN
• 9.) SELF JOIN

Understand SQL Joins with practical examples:

As part of this training session we will try to understand these Joins practically and to achieve this execute below SQL script to create the necessary tables and data for this exercise. For simplicity purpose for this demonstration we have not created any primary or foreign key.

CREATE TABLE Table_A( ID numeric(2, 0) NULL, SampleColumn1 nchar(10));

CREATE TABLE Table_B(ID numeric(2, 0) NULL, SampleCol2 nchar(10));

CREATE TABLE Table_Trans( IDD numeric(2, 0) NULL, SomeTransCol nchar(20) );

Insert into Table_A values(1,'AAA');

Insert into Table_A values(2,'BBB');

Insert into Table_B values(1,'RRR');

Insert into Table_B values(2,'SSS');

Insert into Table_B Values(3,'PPP');

Insert into Table_Trans Values(1, 'Transaction 1');

Insert into Table_Trans Values(3, 'Transaction 2');

Insert into Table_Trans Values(3, 'Transaction 3');

Insert into Table_Trans Values(3, 'Transaction 4');

Insert into Table_Trans Values(1, 'Transaction 5');
commit;

Once the above  script executed table_a,  table_b &  table_trans created with below data.

SELECT * FROM table_a;

SELECT * FROM table_b;

SELECT * FROM table_trans;

1.) CARTESIAN JOIN

• When two or more tables are joined without any condition the resultset will be the multiplication of first table row X Second table row X .......N table row.
• Easiest but rarest in use.
• Cartesian product will take a single record in first table and attach with all the records in second table...third table and so on. Then takes the second record in first table and attach with all the records in second table...third table and so on. This continues till the end of records of first table.
• For Example: SELECT * FROM table_a, table_b;
  
• Same way if we add table_trans in SQL i.e. SELECT * FROM table_a, table_b, table_trans the total result will be 30 rows i.e. 2x3x5.

Why Cartesian join is not practical and why to use other joins by joining the tables with conditions:
Since the result without a condition is too big and not of great usage, having a condition (one column value in first table = one columns value in first table) could eliminate/filter the number of records from the search results, which are not useful and related by common column.

For Example:

SELECT a.id AS A_id, a.samplecolumn1, b.id AS B_id, b.samplecol2 

FROM table_a a, table_b b

WHERE a.id=b.id;

OR

SELECT a.id AS A_id, a.samplecolumn1, b.id AS B_id, b.samplecol2 

FROM table_a a
INNER JOIN 
table_b b

ON a.id=b.id;

2.) INNER JOIN

• Simplest & most commonly used join.
• Query using INNER JOIN will return all of the records in the left table (A) that have a matching record in the right table (B).
  

For Example:

SELECT a.id AS A_id, a.samplecolumn1, b.id AS B_id, b.samplecol2

FROM table_a a, table_b b

WHERE a.id=b.id;

OR

SELECT a.id AS A_id, a.samplecolumn1, b.id AS B_id, b.samplecol2
FROM table_a a
INNER JOIN 
table_b b
ON a.id=b.id;

3.) LEFT JOIN

• Query using LEFT JOIN will return all of the records in the left table (A) regardless if any of those records have a match in the right table (B) or not PLUS It will also return all the matching record from right table (B).
  
  
• LEFT Join is achieved using (+) operator.
• To achieve LEFT Join we need to place (+) at the end of right side table/column in where clause.
• For the rows of table A, which does not have corresponding matching row for table B, (null) is displayed (marked in red rectangle in screenshot below).

NOTE: To complete the next exercise for other joins we need to perform below two statements.

insert into table_a values (4,'CCC'); 

commit;

For Example:

SELECT a.id AS A_id, a.samplecolumn1, b.id AS B_id, b.samplecol2

FROM table_a a, table_b b

WHERE a.id=b.id (+);

OR

SELECT a.id AS A_id, a.samplecolumn1, b.id AS B_id, b.samplecol2

FROM table_a a

LEFT JOIN 

table_b b

ON a.id=b.id;

4.) RIGHT JOIN

• Query using RIGHT JOIN will return all of the records in the right table (B) regardless if any of those records have a match in the left table (A) or not PLUS It will also return all the matching record from left table (B).
  
  
• RIGHT Join is also achieved using (+) operator.
• To achieve RIGHT Join we need to place (+) at the end of left side table/column in where clause.
• For the rows of table B, which does not have corresponding matching row for table A, (null) is displayed (marked in red rectangle in screenshot below).

For Example:

SELECT a.id AS A_id, a.samplecolumn1, b.id AS B_id, b.samplecol2

FROM table_a a, table_b b

WHERE a.id (+)=b.id;

OR

SELECT a.id AS A_id, a.samplecolumn1, b.id AS B_id, b.samplecol2

FROM table_a a

RIGHT JOIN 

table_b b

ON a.id=b.id;

5.) FULL OUTER JOIN or OUTER JOIN or FULL JOIN

• Query using FULL OUTER JOIN or OUTER JOIN will return all of the records of both the tables (matching or not matching result both ).
• For the rows of table A or B, which does not have corresponding matching row (null) is displayed.
  
  

For Example:

SELECT a.id AS A_id, a.samplecolumn1, b.id AS B_id, b.samplecol2

FROM table_a a

FULL OUTER JOIN 

table_b b

ON a.id=b.id;

6.) LEFT EXCLUDING JOIN

• Query using LEFT EXCLUDING JOIN will return all of the records of left table (A) that do not match any records in right table (B).
  

• In order to return all of the records of left table (A) that do not match any records in right table, first you need to LEFT JOIN table A with B, this will give all the record from table A plus all the matching record from right table (B). Thereafter when WHERE b.id IS NULL is applied it display final result which is nothing but, all of the records of left table (A) that do not match any records in right table (B).
• For the rows of table A or B, which does not have corresponding matching row (null) is displayed.

For Example:

SELECT a.id AS A_id, a.samplecolumn1, b.id AS B_id, b.samplecol2

FROM table_a a

LEFT JOIN 

table_b b

ON a.id=b.id

WHERE b.id IS NULL;

7.) RIGHT EXCLUDING JOIN

• Query using RIGHT EXCLUDING JOIN will return all of the records of RIGHT table (B) that do not match any records in left table (A).
  
  

• In order to return all of the records of right table (B) that do not match any records in left table, first you need to RIGHT JOIN table A with B, this will give all the record from table B plus all the matching record from right table (A). Thereafter when WHERE a.id IS NULL is applied, it display final result which is nothing but, all of the records of right table (B) that do not match any records in left table (A).
• For the rows of table A or B, which does not have corresponding matching row (null) is displayed.

For Example:
SELECT a.id AS A_id, a.samplecolumn1, b.id AS B_id, b.samplecol2

FROM table_a a

RIGHT JOIN 

table_b b

ON a.id=b.id

WHERE a.id IS NULL;

8.) OUTER EXCLUDING JOIN

• Query using OUTER EXCLUDING JOIN will return all of the records of LEFT table (A) and all of the records of RIGHT table (B) that do not match.
  
  

• In order to return all of the records of LEFT table (A) and all of the records of RIGHT table (B) that do not match, first you need to FULL OUTER JOIN table A with B, this will give all the record from left table (A) plus all the record from right table (B). Thereafter when WHERE a.id IS NULL OR b.id IS NULL is applied, it display final result which is nothing but; all of the records of right table (B) that do not match any records in left table (A).
• For the rows of table A or B, which does not have corresponding matching row (null) is displayed.

For Example:

SELECT a.id AS A_id, a.samplecolumn1, b.id AS B_id, b.samplecol2

FROM table_a a

FULL OUTER JOIN 

table_b b

ON a.id=b.id

WHERE a.id IS NULL OR b.id IS NULL;

9.) SELF JOIN

• Joining one table with the same tables is called SELF JOIN. (I will explain the  SELF JOIN in my next post in detail).

JOINING MULTIPLE (more than two) TABLES:

While joining more than two tables, first initial two tables are joined and there after the result of first two table is joined with the third table and then final result is displayed.

For Example:

SELECT  a.id AS A_id, a.samplecolumn1, 

              b.id AS B_id, b.samplecol2,

              c.sometranscol

FROM     table_a a, table_b b, table_trans c

WHERE   a.id=b.id

AND        b.id=c.idd;

Sometime UNION, ALL etc could also be used in place of joins, like in below example you can fake it by union two outer joins:

select a.field1, b.field2
from table_a a, table_b b
where a.id = b.id(+)
union all 
select a.field1, b.field2
from table_a a, table b b
where a.id(+) = b.id
      and a.id is null