MySQL JOINs

In RDBMS data stored into multiple tables rather than saving all data into one big storeroom. MySQL Join can help you to retrieve records from two (or more..) logically related tables in a single result set. JOINS are four types.

1. INNER JOIN (EquiJoin ): Returns all matched records
2. Left JOIN: Returns all matched records and the left side table records which are not having any match with the right side table.
3. RIGHT JOIN: Returns all matched records and the right side table records which are not having matched with the left side table.
4. FULL JOIN: Returns all matched records and not matched records from both tables. MySQL doesn’t support Full Joins

When you are joining tow tables we should use one of the table’s primary key columns or unique key columns in join condition otherwise it will generate duplicate records. It is good practice to build foreign key relations between tables, and we can use the foreign key column in the JOIN condition. Many Programming engineers & Developers spend more time to find out join condition columns between tables and building JOIN condition while writing SQL queries.

The SmartWorkbench is the best tool for them & it builds JOIN condition if you drag a table into an existing Query in a fraction of seconds. It can work even if your tables do not have foreign key constants.

What is Inner Join (EquiJoin)?

The most frequently used joins are the INNER JOINs.  The SQL Inner Join returns a row when there is a match between join tables in the Form clause.

For example, if you join the employee table with the departments it returns all records if employee.dep_no match with department.dep_no. It will not return record if any employee dep_no did not exist any match in the department table. Also, it will not return records if any department.dept_no doesn’t have a match in the employee table.

SELECT 
  e.*,
  d.dept_name
FROM
       departments d
  JOIN employee e
    ON (e.dep_no = d.dept_no);

What is Left JOIN?

The SQL Left Join returns a row when there is a match between tables also if there is no match for a left-hand table in the JOIN clause.

For example, if you Left Join the employee table with the departments it returns all records if employee.dep_no matches with department.dep_no. also, It will return record if any employee dep_no has not existed match in the department table. It will not return records if any department.dept_no doesn’t have a match in the employee table.

SELECT 
  e.*,
  d.dept_name
FROM
       departments d
  LEFT JOIN employee e
    ON (e.dep_no = d.dept_no);

What is Right JOIN?

The SQL Right Join returns a row when there is a match between tables also if there is no match for a right-hand table in the JOIN clause.

For example, if you Right Join the employee table with the departments it returns all records if employee.dep_no matches with department.dep_no. also , It will return record if any department dept_no has not existed match in the employee table. It will not return records if any employee.dep_no doesn’t have a match in the department table.

SELECT 
  e.*,
  d.dept_name
FROM
       departments d
  Right JOIN employee e
    ON (e.dep_no = d.dept_no

What is the FULL OUTER JOIN?

It return all matched records and not matched records with null values. MySQL does not support FULL OUTER JOIN. We can write it using UNION operations.

SELECT 
  e.*,
  d.dept_name
FROM
       departments d
  Left JOIN employee e
    ON (e.dep_no = d.dept_no)
union 
SELECT 
  e.*,
  d.dept_name
FROM
       departments d
  Right JOIN employee e
    ON (e.dep_no = d.dept_no) 
where d.dep_no is null.