- Data is stored in multiple tables that are related to each other in relational data bases like SQL Server, MySQL etc with a common key value.
- When we need to get row by combining one or more tables based on common fields between them can be achieved using a SQL Join.
- When we need to extract records from two or more tables into a new table, JOIN clause helps us to acheive this based on certain condition.
- Based on logical relationship between tables join clause helps us to query and access data from multiple tables.
- In other words, Join show how data from a row can be fetched from one table to another table.
Different types of Joins are:
- Inner join/Simple Join
- Left Outer join/Left Join
- Right Outer Join/Right join
- Full Outer join
- Cross Join
- Self Join
Inner Join
- In SQL if a defined condition is valid the inner join will select all the matching rows and columns from both tables.
Syntax
[pastacode lang=”sql” manual=”Select%20column_1%2C%20column_2%20column_3%20from%20table_1%20innerjoin%20table_2%20ON%20table_1.column%20%3D%20table_2.column%3B” message=”” highlight=”” provider=”manual”/]Left Join
- In left join all records are retrived from left table(table1) and matched rows and column from right table(table2).
- If there is no matching rows or columns, left join returns NULL.
Syntax
[pastacode lang=”sql” manual=”Select%20column_1%2C%20column_2%2C%20columns%20from%20Table_1%20left%20join%20table_2%20ON%20table_1.column_name%20%3D%20table_2.column%20name.” message=”” highlight=”” provider=”manual”/]Right Join or Right Outer Join
- Records from the right table are retrived in RIGHT JOIN and the matched rows or columns from the left table(table1).
- If there is no matching rows or columns it will return NULL.
Syntax
[pastacode lang=”sql” manual=”Select%20column_1%2C%20column_2%2C%20cloumn(s)%20FROM%20table_1%20right%20join%20table_2%20ON%20table_1.column_name%3D%20table2.column_name.” message=”” highlight=”” provider=”manual”/]Full Join or Full Outer Join
- This join is a combination of both Left join and right join.
- The joined tables returns all the records from both the tables.
- If no matches are found in the table, this join will return a NULL Value.
- It is also called as Full Outer Join.
Syntax
[pastacode lang=”sql” manual=”Select%20column_1%2C%20column_2%2C%20column(s)%20from%20table_1%20Full%20join%20table_2%20ON%20table_1.column_name%20%3D%20table_2.column_name%3B” message=”” highlight=”” provider=”manual”/]Cross Join
- It is also known as Cartesian Join which returns cartesian product of two or more joined tables.
- Cross join produces a table that merges each rows from the first table with each rows of second table.
- No condition ir required for CROSS JOIN.
Syntax
[pastacode lang=”sql” manual=”Select%20*%20from%20table_1%20cross%20join%20table_2%3B%20%20%0AOr%2C%0ASelect%20column1%2C%20column2%2C%20column3%20from%20table_1%2C%20table_2%3B%20%0A” message=”” highlight=”” provider=”manual”/]Self Join
- Tables formed by joining itself is called Self Join.
- It makes a temporary naming of atleast one table in SQL Statement.
Syntax
[pastacode lang=”sql” manual=”Select%20column1%2C%20column2%2C%20column(s)%20from%20table_1%20Tbl1%2C%20table_2%20Tbl2%20where%20condition%3B%20%20″ message=”” highlight=”” provider=”manual”/]
