top of page

SQL Joins

  • From the very beginning we are talking about the advantages of relational databases and how they help us to make the data retrieve faster and efficient from the relations between the table.

  • SQL Joins are the important concept we should learn which helps to achieve the above task with ease.

  • SQL Joins uses the relation between two tables and join them with a common column.

  • Types of joins are given below.

Joins
Explanation
Inner Join
It will take only matching values from both the tables.
Left Join
It will take the matching values from both the tables and non-matching value from the left table.
Right Join
It will take the matching values from both the tables and non-matching values from right table.
Full Outer Join
It is a combination of left join and right join. It wii consider erverything in both the circle.
Cross Join
It will multiply all the values from both the tables.

Join Syntax

  • Below is the syntax for joining two tables using the common column name by giving them aliases.

  • In the select clause, pass the name of the columns whose records we want to fetch and give the type of join and condition of joining the tables.

  • Instead of using the full names of tables every where give them aliases for our convenience.

Syntax 

SELECT 
       t1.column_name, t1.column_name, t2.column_name, t2.column_name
FROM
      table_1   t1
JOIN 
      table_2   t2
      ON  t1.commom_column = t2.commom_column

Inner Join

  • It will return the matching values from two tables.

  • Instead of inner join, we can simply write join and result will be same.

1 inner join.jpg
Syntax 

SELECT 
       t1.column_name, t1.column_name, t2.column_name, t2.column_name
FROM
      table_1   t1
INNER JOIN 
      table_2   t2
      ON  t1.commom_column = t2.commom_column

Left Join

  • It will return the matching values from two tables and non-matching values from the left table.

  • It is a type of outer join.

  • Instead of left join, we can also write left outer join and result will be same.

Syntax 

SELECT 
       t1.column_name, t1.column_name, t2.column_name, t2.column_name
FROM
      table_1   t1
LEFT JOIN 
      table_2   t2
      ON  t1.commom_column = t2.commom_column

  • If you want only the left table non-matching records and not the matching the records from both the tables then we can achieve that by passing where clause.

Syntax 

SELECT 
       t1.column_name, t1.column_name, t2.column_name, t2.column_name
FROM
      table_1   t1
LEFT JOIN 
      table_2   t2
      ON  t1.commom_column = t2.commom_column
WHERE
       t2.column_name is NULL

Right Join

  • It will return the matching values from two tables and non-matching values from the right table.

  • It is a type of outer join.

  • Instead of right join, we can also write right outer join and result will be same.

Syntax 

SELECT 
       t1.column_name, t1.column_name, t2.column_name, t2.column_name
FROM
      table_1   t1
RIGHT JOIN 
      table_2   t2
      ON  t1.commom_column = t2.commom_column

  • If you want only the right table non-matching records and not the matching the records from both the tables then we can achieve that by passing where clause.

Syntax 

SELECT 
       t1.column_name, t1.column_name, t2.column_name, t2.column_name
FROM
      table_1   t1
RIGHT JOIN 
      table_2   t2
      ON  t1.commom_column = t2.commom_column
WHERE
       t1.column_name is NULL

Full Outer Join

  • It will return the combination of left outer join and right outer join

  • It is a full join.

  • MySQL don't support full joins. Instead we can use UNION in MySQL.

SQL Syntax

SELECT 
       t1.column_name, t1.column_name, t2.column_name, t2.column_name
FROM
      table_1   t1
FULL OUTER JOIN 
      table_2   t2
      ON  t1.commom_column = t2.commom_column
MySQL Syntax 

SELECT 
       t1.column_name, t1.column_name, t2.column_name, t2.column_name
FROM
      table_1   t1
LEFT JOIN 
      table_2   t2 ON  t1.commom_column = t2.commom_column

UNION ALL

SELECT        
      t1.column_name, t1.column_name, t2.column_name, t2.column_name
FROM     
         table_1   t1
RIGHT JOIN    
         table_2   t2 ON  t1.commom_column = t2.commom_column

UNION VS UNION ALL

  • Union will return the distinct values while union all will return all the values.

  • Union Requires more resouces from sql. so it is slower compared to union all.

Cross Join

  • It will return the multiplication of two tables. Each record from first table will be joined with each table on the right side.​

Syntax 

SELECT 
       t1.column_name, t1.column_name, t2.column_name, t2.column_name
FROM
      table_1   t1
CROSS JOIN 
      table_2   t2
bottom of page