Sql Join

Join is used to retrieve data from two or more database tables. Using SQL Joins we can combine records from two or more tables in a database. There are different kinds of join in SQL. The joins are performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT. Joins can also be used in other clauses such as GROUP BY, WHERE, SUB QUERIES, AGGREGATE FUNCTIONS etc. Let’s see one Example to understand Join.

Table 1 − STUDENT Table

Table 2 − RESULT Table

let us join these two tables in our SELECT statement

SELECT ID, NAME, Batch, Grade
FROM STUDENT, RESULT
WHERE STUDENT.ID = RESULT.STUDENT_ID;

This would produce the following result.

Different Types of SQL JOINs

• Inner Join – This Join returns rows when there is a match in both tables.
• Outer Join – There are three types of outer join.
  1. Left Join − This Join returns all rows from the left table, even if there are no matches in the right table.
  2. Right Join − This Join returns all rows from the right table, even if there are no matches in the left table.
  3. Full Join − This Join returns rows when there is a match in one of the tables.
• Self Join − This Join is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.
• Cartesian Join − This Join returns the Cartesian product of the sets of records from the two or more joined tables.

Example of Joins

Consider two table College and Hostel for understands types of join and difference between joins.

Table 1: College

Table 2: Hostel

Output of Inner Join

Output of Left Outer Join

Output of Right Outer Join

Output of Full Outer Join

In short SQL join is used to improve data integrity and reduce the number of queries requirements. Using join easily combine data from multiple tables and get data as per requirements from different tables.

See More: More Popular NoSQL Database