SQL JOIN
- In SQL, JOIN means “to combine two or more tables”.based on a related column between them.
- The SQL JOIN clause takes records from two or more tables in a database and combines it together.
- The joining of two or more tables is based on common field between them.
- If you want to access more than one table through a select statement.SQL join is used
- In the process of joining, rows of both tables are combined in a single table.
- Five types of JOIN:
- Inner Join,
- Left join,
- Right join,
- Full join, and
- Cross join.
SQL Inner Join
The INNER JOIN keyword selects records that have matching values in both tables.
example to deploy SQL JOIN process:
1.Staff table
| ID | Staff_NAME | Staff_AGE | STAFF_ADDRESS | Monthley_Package |
| 1 | Arjun | 23 | Nagpur | 18000 |
| 2 | Suhas | 33 | Devgad | 21000 |
| 3 | Mohan | 27 | Mumbai | 23000 |
| 4 | Amol | 21 | Elhabad | 12100 |
2.Payment table
| Payment_ID | DATE | Staff_ID | AMOUNT |
| 101 | 30/12/2009 | 1 | 3000.00 |
| 102 | 23/02/2010 | 3 | 2700.00 |
| 103 | 23/02/2010 | 4 | 3500.00 |
Following is the JOIN statement to join these two tables :
SELECT Staff_ID, Staff_NAME, Staff_AGE, AMOUNT
FROM STAFF s, PAYMENT p
WHERE s.ID =p.STAFF_ID; Output result like this:
| STAFF_ID | NAME | Staff_AGE | AMOUNT |
| 3 | Mohan | 27 | 2700 |
| 1 | Arjun | 23 | 3000 |
| 4 | Amol | 27 | 3500 |
| 1 | Arjun | 23 | 3000 |
SQL LEFT JOIN
The SQL left join returns all the values from the left table(table1) and it also includes matching values from right table(table2).
If there are no matching join value it returns NULL.
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:
CUSTOMER TABLE1:
| ID | NAME | AGE | SALARY |
| 1 | Arjun | 51 | 56000 |
| 2 | Saloni | 21 | 27000 |
| 3 | Vandana | 24 | 31000 |
| 4 | Anurag | 23 | 33000 |
| 5 | Priya | 23 | 42100 |
ORDER TABLE2:
| O_ID | DATE | CUSTOMER_ID | AMOUNT |
| 001 | 21-01-2012 | 2 | 3000 |
| 002 | 12-02-2012 | 2 | 2100 |
| 003 | 23-03-2012 | 3 | 4000 |
| 004 | 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; This will produce the following Output:
| ID | NAME | AMOUNT | DATE |
| 1 | Arjun | NULL | NULL |
| 2 | Saloni | 3000 | 21-01-2012 |
| 2 | Saloni | 2100 | 12-02-2012 |
| 3 | Vandana | 4000 | 23-03-2012 |
| 4 | Anurag | 5000 | 11-04-2012 |
| 5 | Priya | NULL | NULL |
SQL RIGHT JOIN
The SQL right join returns all the values from the rows of right table(Table2) and also includes the matched values from left table (table1). if there is no matching in both tables, it returns NULL.
Syntax for right join:
SELECT table1.column1, table2.column2.....
FROM table1
RIGHT JOIN table2
ON table1.column_field = table2.column_field; let us take an example with 2 tables table1 is CUSTOMERS table and table2 is ORDERS table.
CUSTOMER TABLE1:
| ID | NAME | AGE | SALARY |
| 1 | Arjun | 51 | 56000 |
| 2 | Saloni | 21 | 27000 |
| 3 | Vandana | 24 | 31000 |
| 4 | Anurag | 23 | 33000 |
| 5 | Priya | 23 | 42100 |
ORDER TABLE2:
| DATE | O_ID | CUSTOMER_ID | AMOUNT |
| 21-01-2012 | 001 | 2 | 3000 |
| 12-02-2012 | 002 | 2 | 2100 |
| 23-03-2012 | 003 | 3 | 4000 |
| 11-04-2012 | 004 | 4 | 5000 |
Here we will join these two tables with SQL RIGHT JOIN:
SELECT ID,NAME,AMOUNT,DATE
FROM CUSTOMER
RIGHT JOIN ORDER
ON CUSTOMER.ID = ORDER.CUSTOMER_ID; | ID | NAME | AMOUNT | DATE |
| 2 | Saloni | 3000 | 21-01-2012 |
| 2 | Saloni | 2100 | 12-02-2012 |
| 3 | Vandana | 4000 | 23-03-2012 |
| 4 | Anurag | 5000 | 11-04-2012 |
SQL FULL JOIN
The result of combination of both left and right outer join and the join tables have all the records from both tables is known as SQL full join is. It puts NULL on the place of matches not found.
SQL full outer join and SQL join are same.
SQL full outer join:
The FULL OUTER JOIN keyword return all records when there is a match in left (table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!
Tip: FULL OUTER JOIN and FULL JOIN are the same.
Syntax for full outer join:
Let us take two tables to demonstrate full outer join:
table_A
| Numbers | Character_A |
| 1 | m |
| 2 | n |
| 4 | o |
table_B
| Numbers | Charactor_B |
| 2 | p |
| 3 | q |
| 5 | r |
Resulting table
| Numbers | Character_A | Numbers | Charactor_B |
| 2 | n | 2 | p |
| 1 | m | – | – |
| 4 | o | – | – |
| – | – | 3 | q |
| – | – | 5 | r |
Because this is a full outer join so all rows (both matching and non-matching) from both tables are included in the output. Here only one row of output displays values in all columns because there is only one match between table_A and table_B.
SQL Cross Join
In general words we can say that SQL CROSS JOIN returns the Cartesian product of the sets of rows from the joined table.When each row of first table is combined with each row from the second table, known as Cartesian join or cross join
We can specify a CROSS JOIN in two ways Using the JOIN syntax or the table in the FROM clause without using a WHERE clause.
SYNTAX of SQL Cross Join
SELECT * FROM [TABLE1] CROSS JOIN [TABLE2]
OR
SELECT * FROM [ TABLE1] , [TABLE2] Let us take an example of two tables,
Table1 – MatchScore
| Player | Department_id | Goals |
| Rohan | 1 | 2 |
| Amruta | 1 | 3 |
| Pariniti | 2 | 2 |
| Rutuja | 3 | 5 |
Table2 – Departments
| Department_id | Department_name |
| 1 | IT |
| 2 | HR |
| 3 | Marketing |
SQL Statement:
SELECT * FROM MatchScore CROSS JOIN Departments After executing this query , you will find the following result:
| Player | Department_id | Goals | Depatment_id | Department_name |
| Rohan | 1 | 2 | 1 | IT |
| Amruta | 1 | 3 | 1 | IT |
| Pariniti | 2 | 2 | 1 | IT |
| Rutuja | 3 | 5 | 1 | IT |
| Rohan | 1 | 2 | 2 | HR |
| Amruta | 1 | 3 | 2 | HR |
| Pariniti | 2 | 2 | 2 | HR |
| Rutuja | 3 | 5 | 2 | HR |
| Rohan | 1 | 2 | 3 | Marketing |
| Amruta | 1 | 3 | 3 | Marketing |
| Pariniti | 2 | 2 | 3 | Marketing |
| Rutuja | 3 | 5 | 3 | Marketing |