_edited_edited.png)
SQL (Structured Query Language) is a programming language for storing, manipulating and retrieving data in relational databases. In a relational database, the data is stored in different data tables. These data tables are related to each other in one way or another.
SQL join statements are used to combine or retrieve data from two or more tables in a database based on the common fields between them. The join query will combine columns from tables to a new result table to present the query data. Below are the commonly used joins in SQL:
1) INNER JOIN
The inner join query compares each row of Table1 with each row of Table2 to find all pairs of rows which satisfy the join. When the join condition is satisfied by matching non-NULL values, column values for each matched pair of rows of Table 1 and Table 2 are combined into a result set. Inner joins are useful in situations when we need to fetch NOT NULL records.
Syntax:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
Example:
Note: Inner Join will eliminate null values from the result sets. We can also write JOIN instead of INNER JOIN. JOIN works same as INNER JOIN.
2) LEFT JOIN
A left join returns all the records from an inner join plus all additional records from the left table that do not match to the right table which in turn will print NULL. Left Joins are useful in situations where we need to fetch all the data from left table nevertheless the table it joins with has matching data or not.
Syntax:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
Example:
Note: We can also use LEFT OUTER JOIN instead of LEFT JOIN.
3) RIGHT JOIN
Right join closely relates to left join. In this join, the right table gets importance. Right join returns all the rows of the right table and matching rows of the left table. If there is any non-matching row from the left table exists, NULL is printed. Right joins are useful in situations where we need to retrieve all rows from the right table.
Syntax:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Example:
Note: We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both are same.
4) FULL JOIN
Full join is a combination of INNER JOIN, LEFT JOIN and RIGHT JOIN. The result set contains all the rows from both the tables. If there are any rows with non-matching values in the other table, it shows NULL values in the result set.
Syntax:
SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;
Example:
Note: We can also use FULL OUTER JOIN instead of FULL JOIN.
5) CROSS JOIN
Cross joins match each row from first table with each row of the second table and returns the Cartesian product. It does not use any join condition (ON clause).
Syntax:
SELECT column_name(s)
FROM table1
CROSS JOIN table2
Example:
6) NATURAL JOIN
Natural join automatically combines tables based on columns with the same name and the data type without using any ON clause. It will perform a cross join if like columns are not found. Natural join does not generate any duplicate columns in the output table. We can combine left, right and full join with natural join.
Syntax:
SELECT column_name(s)
FROM table1
NATURAL JOIN table2
Example:
7) SELF JOIN
Unlike other joins, self join is used to join a table to itself. Each row of the table is combined with itself and with every other row of the table. The self join can be viewed as a join of two copies of the same table.
Syntax:
SELECT a.column_name, b.column_name
FROM table1 a
INNER JOIN table1 b
ON a.common_field = b.common_field;
Example: To find courses that more than one student is enrolled in.
CONCLUSION:
Different types of joins can be used to extract data from two or more data tables based on the requirement. Advantages of using joins are execution and retrieval time of a query will be faster and also it will minimize the calculation burden on the database.
I hope this blog is informative to you!
Happy Learning!!!