A SQL join is an SQL clause that is used to join records from two or more tables in a database. The records from the tables are combined by a common attribute that the tables share, resulting in a single record set. This record set can be inserted into a temporary table to be stored and used. You can also specify additional criteria in the join to filter out records (besides sharing the common attribute). There are four types of SQL joins: Inner, Outer (Full), Left, and Right.
Inner SQL Joins
Inner joins are the most commonly used. They only combine records from the two tables if they both match the join condition (share a common attribute). This joins work best when referential integrity is enforced in the database, especially on primary and foreign keys.
SELECT Employee_Name, Employee_ID, Department_name, Dept_Code
FROM EMPLOYEE INNER JOIN DEPARTMENT
ON EMPLOYEE.Dept_Code = DEPARTMENT.Dept_Code
Outer SQL Joins
Outer joins combine everything from both tables together, even if both records do not share a matching value. They can be useful if you need to combine two tables together and want to keep all of the rows, or want a large result set.
SELECT Employee_Name, Employee_ID, Department_name, Dept_Code
FROM EMPLOYEE OUTER JOIN DEPARTMENT
ON EMPLOYEE.Dept_Code = DEPARTMENT.Dept_Code
Left SQL Joins
Left joins are a type of outer join that retains all of the records of the left table and include only records from the right table that match the join condition (share a common attribute). Left joins are similar to an inner join in that you are getting all of the related records that both tables share, but all of the left table’s records are included in the result set whether they match the join condition or not. Left joins are particularly useful if you are joining two tables on a value that may be NULL (blank/empty) in the right table.
SELECT Employee_Name, Employee_ID, Department_name, Dept_Code
FROM EMPLOYEE LEFT JOIN DEPARTMENT
ON EMPLOYEE.Dept_Code = DEPARTMENT.Dept_Code
Right SQL Joins
Right joins are exactly the same as left joins except that all of the records from the right table are retained instead. Records from the left table that match the join condition (share a common attribute) will still be included in the result set as well. Right joins come in handy when the left table may contain NULLS (blanks) in the common field that is being used to join.
SELECT Employee_Name, Employee_ID, Department_name, Dept_Code
FROM EMPLOYEE RIGHT JOIN DEPARTMENT
ON EMPLOYEE.Dept_Code = DEPARTMENT.Dept_Code
SQL joins are commonplace in the database world as normalized databases contain many tables. Without them, there would not be an efficient way to retrieve the data that you are looking for. Utilizing the four types will help you write better queries, and retrieve the proper data efficiently.