SQL Join | Inner, Left, Right and Outer Join
JOINS in SQL are commands which are used to combine rows from two or more tables, based on a related column between those tables.
We can join the table using a SELECT statement and a join condition. It indicates how SQL Server can use data from one table to select rows from another table. In general, tables are related to each other using foreign key constraints.
SQL Server mainly supports four types of JOINS, and each join type defines how two tables are related in a query. The following are types of join supports in SQL Server:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
INNER JOIN -
It is considered as a default join. It is the simplest and most popular form of join. This join returns the result from the selected table that satisfies the given Join condition. In the output result we will get only those rows who have matching records on both the tables.
The following syntax illustrates the use of INNER JOIN in SQL Server:
SELECT column_lists
FROM table1
INNER JOIN table2 ON condition1;
LEFT JOIN -
This join returns all the values from the left table & matching rows from the right table. The rows for which there are no records in the right table will return the null in the output.
The following syntax illustrates the use of LEFT JOIN in SQL Server:
SELECT column_lists
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
RIGHT JOIN -
This join returns all the values from the right table & matching rows from the left table. The rows for which there are no records in the left table will return the null in the output.
The following syntax illustrates the use of RIGHT JOIN in SQL Server:
SELECT column_lists
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
OUTER JOIN -
The OUTER JOIN in SQL Server returns a result that includes all rows from both tables. The columns of the right-hand table return NULL when no matching records are found in the left-hand table. And if no matching records are found in the right-hand table, the left-hand table column returns NULL.
The following syntax illustrates the use of OUTER JOIN in SQL Server:
SELECT column_lists
FROM table1
OUTER JOIN table2
ON table1.column = table2.column;
No comments: