SQL OUTER JOIN

The outer join is type of join that retrieves all rows from both left and right tables.

The column values that do not exist in either table will be filled with null value.

Outer join of two types:
  1. Left outer join (also known as left join): this join returns all the rows from left table combine with the matching rows of the right table. If you get no matching in the right table it returns NULL values.
  2. Right outer join(also known as right join): this join returns all the rows from right table are combined with the matching rows of left table .If you get no column matching in the left table .it returns null value.

How to use SQL left join with example

The left join is the type of join that returns all rows from left table (the table in select from side) whereas only matched rows from other table are retrieved.

As it returns all rows from first table/left table, the column values for right table will be shown as Null.

BASIC SYNTAX FOR LEFT JOIN:

SELECT table1.column1, table2.column2....   

FROM table1    

LEFTJOIN table2   

ON table1.column_field = table2.column_field;      

					

let us take two tables in this example to elaborate all the things:

Customer Table
ID NAME AGE SALARY
1 Aryan 51 522
2 Hema 25 2563
3 Vinay 20 3000
4 Mithilesh 26 32000
5 Ravi 23 42000

this is the second table

ORDER TABLE

O_ID DATE CUSTOMER_ID AMOUNT
001 20-01-2012 2 3000
02 12-02-2012 2 2000
3 22-03-2012 3 4000
4 11-04-2012 4 5000
join these two tables with LEFT JOIN:

					SQL SELECT ID, NAME, AMOUNT,DATE  

FROM CUSTOMER   

LEFT JOIN ORDER  

ON CUSTOMER.ID = ORDER.CUSTOMER_ID;

					

SQL RIGHT JOIN

The right join of SQL is the type of join that returns all rows from right table (the table “joined”) whereas only matched rows from other table are retrieved.

As it returns all rows from second table/right table, the column values for left table will be shown as Null.

Basic syntax for right join:

					SELECT table1.column1, table2.column2.....   

FROM table1    

RIGHT JOIN table2   

ON table1.column_field = table2.column_field; 

					

Here we will join these two tables with SQL RIGHT JOIN:


					SQL> SELECT ID,NAME,AMOUNT,DATE  

FROM CUSTOMER   

RIGHT JOIN ORDER  

ON CUSTOMER.ID = ORDER.CUSTOMER_ID;