INNER JOIN/simple join LEFT OUTER JOIN/LEFT JOIN RIGHT OUTER JOIN/RIGHT JOIN FULL OUTER JOIN
INNER JOIN
This type of SQL server JOIN returns rows from all tables in which the join condition is true. It takes the following syntax: We will use the following two tables to demonstrate this: Students Table:
Fee table:
The following command demonstrates an INNER JOIN in SQL server with example: The command returns the following:
We can tell the students who have paid their fee. We used the column with common values in both tables, which is the admission column.
LEFT OUTER JOIN
This type of join will return all rows from the left-hand table plus records in the right-hand table with matching values. For example: The code returns the following:
The records without matching values are replaced with NULLs in the respective columns.
RIGHT OUTER JOIN
This type of join returns all rows from the right-hand table and only those with matching values in the left-hand table. For example: The statement for OUTER JOINS SQL server returns the following:
The reason for the above output is that all rows in the Fee table are available in the Students table when matched on the admission column.
FULL OUTER JOIN
This type of join returns all rows from both tables with NULL values where the JOIN condition is not true. For example: The code returns the following result for FULL OUTER JOINS queries in SQL:
