SQL JOINS

Hi all,
The SQL joins are used for combine rows from two or more table based on a common field between them.We are discuss this topic today. It fetch data from two or more tables and which is joined to appear as single set of data. Join Keyword is used in SQL queries for joining two or more tables. Minimum required condition for joining table, is (n-1) where n, is number of tables.

Different types of JOINs are available in SQL .They are:

• INNER JOIN
• LEFT JOIN






• RIGHT JOIN
• FULL JOIN

1) INNER Join or EQUI Join

The INNER JOIN selects all rows from both tablesresults based on the match between the columns in both tables. Returns all the rows when there is atleast one matches in both tables.

SYNTAX

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

or

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;

Example of INNER JOIN. The Employee1 table

Employee_ID	Employee_Name
1	Jose
2	Thomas
3	Joseph

The Employee2 table

Employee_ID	Salary
1	4000
2	8000

Inner JOIN query will be,

SELECT * from Employee1, Employee2 where Employee1.Employee_ID = Employee2.Employee_ID;

The result will be

Employee_ID	Employee_Name	Employee_ID	Salary
1	Jose	1	4000
2	Thomas	2	8000

2) LEFT Join

Return all rows from the left table, and the matched rows from the right table.

SYNTAX

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

or

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Example of LEFT JOIN. The Employee1 table

Employee_ID	Employee_Name
1	Jose
2	Thomas
3	Joseph

The Employee2 table

Employee_ID	Salary
1	4000
2	8000

LEFT JOIN query will be,

SELECT Employee_ID, Employee_Name,Salary
FROM Employee1
LEFT JOIN Employee2
ON Employee1.Employee_ID = Employee2.Employee_ID;

The result will be

Employee_ID	Employee_Name	Salary
1	Jose	4000
2	Thomas	8000
3	Joseph	NULL

The LEFT JOIN keyword returns all rows from the left table (Employee1), with the matching rows in the right table (Employee2).

3) RIGHT Join

The right join return all rows from the right table, and the matched rows from the left table.

SYNTAX

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

or

SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;

Example of RIGHT JOIN. The Employee1 table

Employee_ID	Employee_Name
1	Jose
2	Thomas
3	Joseph

The Employee2 table

Employee_ID	Salary
1	4000
2	8000

RIGHT JOIN query will be,

SELECT Employee_ID, Employee_Name,Salary
FROM Employee1
RIGHT JOIN Employee2
ON Employee1.Employee_ID = Employee2.Employee_ID;

The result will be

Employee_ID	Employee_Name	Salary
1	Jose	4000
2	Thomas	8000

The RIGHT JOIN keyword returns all rows from the right table (Employee2), with the matching rows in the left table (Employee1).

4) FULL OUTER Join

Return all the rows from the left table and right table

SYNTAX

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

Example of FULL OUTER JOIN. The Employee1 table

Employee_ID	Employee_Name
1	Jose
2	Thomas
3	Joseph

The Employee2 table

Employee_ID	Salary
1	4000
2	8000

RIGHT JOIN query will be,

SELECT Employee_Name,Salary
FROM Employee1
FULL OUTER JOIN Employee2
ON Employee1.Employee_ID = Employee2.Employee_ID;

The result will be

Employee_Name	Salary
Jose	4000
Thomas	8000
Joseph

If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.