JOINS

JOINS

JOIN is same as INNER JOIN and means to only show records common to both tables. Whether the records are common is determined by the fields in the join clause. 

 

Employee	Location
EmpID EmpName emp_manager_id 13 Sandeep NULL 9 Kailash 1 4 Ram 1 17 Nitin 5 25 Yogesh 2	EmpID EmpLoc 13 San Jose 9 Los Angeles 4 Pune, India 17 Chennai, India 39 Bangalore, India

INNER JOIN:

Inner join returns only those records/rows that match/exists in both the tables. 

SQL Query:

SELECT e.emp_id, e.Emp_name, l.Emp_loc FROM employee AS e INNER JOIN location AS l ON e.emp_id=l.emp_id

After Execution: 

EmpID	Emp_Name	EmpLoc
13	Sandeep	San Jose
9	Kailash	Los Angeles
4	Ram	Pune, India
17	Nitin	Chennai, India

LEFT JOIN:

LEFT JOIN is same as LEFT OUTER JOIN and A left outer join retains all of the rows of the “left” table means to show all records from the left table (i.e. the one that precedes in SQL statement) regardless of the existence of matching records in the right table. 

SQL Query:

SELECT e.emp_id, e.Emp_name, l.Emp_loc FROM employee AS e LEFT JOIN location AS l ON e.emp_id=l.emp_id

After Execution: 

EmpID	Emp_Name	EmpLoc
13	Sandeep	San Jose
9	Kailash	Los Angeles
4	Ram	Pune, India
17	Nitin	Chennai, India
23	Yogesh	NULL

 

RIGHT JOIN:

RIGHT JOIN is same as RIGHT OUTER JOIN and it returns all the rows from the right table means shows all records from the second (right) table and only matching records from first (left) table are displayed in the result set means opposite of LEFT JOIN. 

SQL Query:

SELECT e.emp_id, e.Emp_name, l.Emp_loc FROM employee AS e RIGHT JOIN location AS l ON e.emp_id=l.emp_id

After Execution: 

EmpID	Emp_Name	EmpLoc
13	Sandeep	San Jose
9	Kailash	Los Angeles
4	Ram	Pune, India
17	Nitin	Chennai, India
NULL	NULL	Mumbai, India

FULL OUTER JOIN:

A standard SQL FULL OUTER join is like a LEFT or RIGHT join, except that it includes all rows from both tables, matching them where possible and filling in with NULLs where there is no match. I’ll illustrate that for clarity. Here are two of my favorite tables, apples and oranges: 

Basic syntax as follow:

SELECT t1.column1, t1.column2, t1.column3, t2.column1, t2.column2 FROM table1 t1 FULL OUTER JOIN table2 t2  ON t1.common_field = t2.common_field

SQL syntax:

SELECT e.Emp_id, e.Name, e.Surname, d.Dept_Name FROM employee e FULL OUTER JOIN department d  ON e.dept_id = d.dept_id

NOTE: If your Database does not support FULL JOIN like MySQL does not support FULL JOIN, then you can use UNION ALL clause to combine two JOINS as follows:

MySQL Query:

SELECT e.Emp_id, e.Name, e.Surname, e.Salary, d.Dept_Name FROM employee e LEFT JOIN department d  ON e.dept_id = d.dept_id union all  SELECT e.Emp_id, e.Name, e.Surname, e.Salary, d.Dept_Name FROM employee e RIGHT JOIN department d  ON e.dept_id = d.dept_id

After Execution: 

EmpID	Emp_Name	EmpLoc
13	Sandeep	San Jose
9	Kailash	Los Angeles
4	Ram	Pune, India
17	Nitin	Chennai, India
23	Yogesh	NULL
NULL	NULL	Mumbai, India

CROSS JOIN:

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are a multiplication of record number from both the tables. 

SQL Query:

SELECT e.*,d.* FROM employee e, department d

After Execution:

EmpID	Emp_Name	EmpLoc
13	Sandeep	San Jose
9	Kailash	San Jose
4	Ram	San Jose
17	Nitin	San Jose
23	Yogesh	San Jose
13	Sandeep	Los Angeles
9	Kailash	Los Angeles
4	Ram	Los Angeles
17	Nitin	Los Angeles
23	Yogesh	Los Angeles
13	Sandeep	Pune, India
9	Kailash	Pune, India
4	Ram	Pune, India
17	Nitin	Pune, India
23	Yogesh	Pune, India
13	Sandeep	Mumbai, India
9	Kailash	Mumbai, India
4	Ram	Mumbai, India
17	Nitin	Mumbai, India
23	Yogesh	Mumbai, India
13	Sandeep	Chennai, India
9	Kailash	Chennai, India
4	Ram	Chennai, India
17	Nitin	Chennai, India
23	Yogesh	Chennai, India

SELF JOIN:

You can join a single table to itself.  In this case, you are using the same table twice. 

Example Table:

Emp_ID	Emp_Name	Emp_Mngr_Id
1	Sandeep	NULL
2	Kailash	1
3	Ram	1
4	Nitin	2
5	Yogesh	2
6	Sneha	5
7	Bhavesh	5

SQL Query:

SELECT e1.Emp_name 'manager', e2.Emp_name 'employee' FROM employees e1 JOIN employees e2 ON e1.emp_id=e2.emp_mngr_id

After Execution: 

Emp_ID	Manager	Employee
1	Sandeep	Kailash
1	Sandeep	Ram
2	Kailash	Nitin
2	Kailash	Yogesh
5	Yogesh	Sneha
5	Yogesh	Bhavesh