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.
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.​