Author - StudySection Post Views - 22 views

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

Emp_no Name Age City
1 Amit 26 Ambala
2 Sumit 28 Delhi
3 Priya 27 Ambala
4 Sakshi 26 Shimla

Department

Dept_id Emp_no
d1 1
d1 5
d3 2
  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.

Leave a Reply

Your email address will not be published.