Home / mySQL / SQL JOINS

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;

inner_join

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;

left_join

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;

right_join

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;

full_join

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.

Loading Facebook Comments ...
Social Media Auto Publish Powered By : XYZScripts.com