What Are SQL Joins?
In this tutorial, we are going to learn about SQL Joins and there types. I will discuss the various types of Joins used in SQL. A SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. There are mostly used when a handler is trying to extract data from tables that have one-to-many or many-to-many relationships between them.
Different types of SQL Joins that you need to understand are.
- INNER JOIN: Select records that have matching values in both tables.
- FULL JOIN: Selects all records that match either left or right table records.
- LEFT JOIN: Select records from the first (left-most) table with matching right table records.
- RIGHT JOIN: Select records from the second (right-most) table with matching left table records.
For your better understanding of this concept, I will be considering the following two tables to show you how to perform the Join operations on such tables.
Student Table.
Class_info Table.
Inner Join.
Inner join returns those records which have matching values in both tables. This inner join will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be the same.
Inner Join Syntax.
SELECT table A.column1, table A.column2, table2.column1,
FROM table A
INNER JOIN table B
ON table A.matching_column = table B.matching_column;
Below is a diagram image to understand the Inner join.
Example of Inner join.
This query will show the student id first name, last name, class info, and address of students on the basis of matching ids in both tables.
SELECT students.id, students.first_name, students.last_name, class_info.class_name, class_info.address FROM students INNER JOIN class_info ON students.id=class_info.id
The output of the above inner join Query.
Note: You can use keywords inner join or join both have the same result.
Left Join OR Left outer Join.
Left join returns all the records of the table on the left side of the join and matching records for the table on the right side of the join. The records for which there is no matching record on the right side, the result-set will contain a null value.
Syntax of Left join or Left Outer Join.
SELECT table A.column1, table A.column2, table B.column1,
FROM table A
LEFT JOIN table B
ON table A.matching_column = table B.matching_column;
Below is a diagram image to understand the Left join.
Example of Left join OR Left outer join.
SELECT students.first_name,students.last_name, class_info.class_name, class_info.address FROM students LEFT JOIN class_info ON students.id = class_info.id
The output of the above Left join OR Left outer join Query.
Right, Join OR Right outer Join.
Right, join returns all the records of the table on the right side of the join and matching records for the table on the left side of the join. The records for which there is no matching record on the left side, the result-set will contain a null value.
Syntax of Right join or Right outer Join.
SELECT table A.column1, table A.column2, table B.column1,
FROM table A
RIGHT JOIN table B
ON table A.matching_column = table B.matching_column;
Below is a diagram image to understand the Right join.
Example of Right join OR Right outer join.
SELECT students.first_name,students.last_name, class_info.class_name, class_info.address FROM students RIGHT JOIN class_info ON students.id = class_info.id
The output of the above Right join OR Right outer join Query.
Full Join.
For a list of all records from both tables, we can use a full join. The second name of this join is (full outer join) there is no difference between (full join) and (full outer join).
Syntax of full join or full outer join.
SELECT Table A.Column1, Table A.Column2, Table B.Column1,
FROM Table A
FULL JOIN Table B
ON Table A.MatchingColumnName = Table B.MatchingColumnName;
Below is a diagram image to understand the full join.
Example of full join OR full outer join.
This query will show all records of both tables on the basis of matching ids in both tables the result-set will contain the NULL values.
SELECT students.first_name, students.last_name, students.age, class_info.class_name, class_info.address FROM students FULL JOIN class_info ON students.id = class_info.id;
The output of the above full join OR full outer join Query.
Note: Sometimes Full join is not work in SQL you can use the union method (a mixture of left join and right join) below is the query with union method you can use it instead of above full join query for getting the same result. And you can use keywords full join or full outer join both have the same result.
SELECT students.first_name, students.last_name, students.age, class_info.class_name, class_info.address FROM students left JOIN class_info ON students.id=class_info.id
UNION
SELECT students.first_name, students.last_name, students.age,class_info.class_name, class_info.address FROM students right JOIN class_info ON students.id=class_info.id
The output of the above full join OR full outer join Query using the union method.
Other Related Posts
How to use PHP for Loop and foreach Loop
How to use PHP while Loop and do while Loop
How to Use PHP arrays and There Types
Insert update and delete data from Database (CRUD) in PHP
We hope you may learn how to use SQL inner join, full join, left join, and right join. Please share your thoughts and queries in the contact section.