What Are SQL Joins?

Share on FacebookTweet about this on TwitterShare on LinkedInGoogle+

Share on FacebookTweet about this on TwitterShare on LinkedInGoogle+

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.

segue-blog-what-is-sql-join

 

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.

Inner Joins

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.

Outer Joins

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.

Left Joins

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.

Right Joins

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.