SQL Join

As the name suggests, combining two or more things together is known as Join.

In terms of SQL, when we have to combine two or more tables on the basis of certain conditions then it is called SQL Join.

There are 4 types of SQL Join:

1. inner join
2. left join
3. right join
4. full join

Employee

Department

1. INNER JOIN: As long as the condition satisfies this join selects all the rows from both the tables.
   Example:
   SELECT department.Dept_id,employee.NAME,employee.Age FROM employee
INNER JOIN department
ON employee.Emp_no = department.Emp_no;
   The above query shows the name and age of the employee working in different departments.
   Output:

   Dept_id	Name	Age
   d1	Amit	26
   d3	Sumit	28

2. LEFT JOIN: fetch all the rows of the table that is on the left side of the join and only those rows that fulfill the required conditions are from the table that is on the right side of the join.
   It is also known by the name LEFT OUTER JOIN.

   Example:
   SELECT employee.Name,department.Dept_id
FROM employee
LEFT JOIN department
ON department.Emp_no=employee.Emp_no;

   Output:

   Name	Dept_id
   Amit	d1
   Sumit	d3
   Priya	null
   Sakshi	null

3. RIGHT JOIN: This join is similar to LEFT JOIN. It fetches all the rows of the table that is on the right side of the join and only those rows that fulfill the required conditions are from the table that is on the left side of the join.
   It is also known by the name RIGHT OUTER JOIN.

   Example:
   SELECT employee.Name,department.Dept_id
FROM employee
RIGHT JOIN department
ON department.Emp_no=employee.Emp_no;

   Output:

   Dept_id	Name
   d1	Amit
   d1	null
   d3	Sumit

4. FULL JOIN: This join is the combination of both the joins LEFT JOIN and RIGHT JOIN. It fetches all the rows from both tables.
   Example:
   SELECT employee.Name,department.Dept_id
FROM employee
FULL JOIN department
ON department.Emp_no=employee.Emp_no;

   Output:

   Name	Dept_id
   Amit	d1
   Sumit	d3
   Priya	null
   Sakshi	null

If you have skills in PHP programming and you want to enhance your career in this field, a PHP certification from StudySection can help you reach your desired goals. Both beginner level and expert level PHP certification exams are offered by StudySection along with other programming certification exams.

Share this...

Facebook

Twitter

Linkedin