Joins

SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are as follows:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN
• NATURAL JOIN
• CROSS JOIN
• SELF JOIN

1. INNER JOIN :

The INNER JOIN keyword selects all rows from both tables as long as the condition is satisfied. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e. the value of the common field will be the same.

Consider the two tables below as follows:

Student

Student’s Table

Course

Course Table

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;

SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

OUTPUT:

2. LEFT JOIN:

This join returns all the rows of the table on the left side of the join and matches rows for the table on the right side of the join. For the rows for which there is no matching row on the right side, the result set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN.

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

SELECT Student.NAME,StudentCourse.COURSE_ID 
FROM Student
LEFT JOIN StudentCourse 
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

OUTPUT:

3. RIGHT JOIN:

The Right Join or Right Outer Join query in SQL returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in the left table; the join will still return a row in the result, but with a NULL value in each column of the left table.

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

SELECT Student.NAME,StudentCourse.COURSE_ID 
FROM Student
RIGHT JOIN StudentCourse 
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

OUTPUT:

4. FULL JOIN:

SQL Full Join creates a new table by joining two tables as a whole. The joined table contains all records from both tables and fills in NULLs for missing matches on either side. In short, a full join is a type of outer join that combines the results of both left and right joins.

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

SELECT Student.NAME,StudentCourse.COURSE_ID 
FROM Student
FULL JOIN StudentCourse 
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

5. NATURAL JOIN:

Natural join can join tables based on the common columns in the tables being joined. A natural join returns all rows by matching values in common columns having the same name and data type of columns and that column should be present in both tables.

Both tables must have at least one common column with the same column name and the same data type.

The two tables are joined using cross-join.

DBMS will look for a common column with the same name and data type Tuples having exactly the same values in common columns are kept in the result.

6. CROSS JOINS:

The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join.

Suppose that we are sitting in a coffee shop and we decide to order breakfast. Shortly, we will look at the menu and we will start thinking of which meal and drink combination could be tastier. Our brain will receive this signal and begin to generate all meal and drink combinations.

7. SELF JOIN:

The SELF JOIN in SQL, as its name implies, is used to join a table to itself. This means that each row in a table is joined to itself and every other row in that table. However, referencing the same table more than once within a single query will result in an error. To avoid this, SQL SELF JOIN aliases are used.

SELECT
    column_names
FROM
    Table1 t1
[INNER | LEFT]  JOIN Table1 t2 ON
    join_predicate;

You can also create the SELF JOIN with the help of the WHERE clause.

SELECT 
    column_names
FROM 
    Table1 t1, Table1 t2
WHERE 
    condition;