SQL Joins: A Comprehensive Beginner’s Guide

Introduction 

SQL joins is a method of extracting data from one or more data tables. In a relational database, this operation is used to link and combine columns from tables based on logical relationships between the tables. This creates a new set of data which can then be used as-is or saved for further use. This join query in SQL can be applied to base tables, views, or joined tables within a single database. 

This guide explains how data from separate tables can be combined into a single data set in SQL. However, commands may vary based on the specific database management system in use. So, it is best to look up the official documentation for exact syntax and related command options.

In this article let us look at:

1. What is SQL Joins?
2. Types of Joins in SQL
3. Use of Join in SQL

1. What is SQL Joins?

SQL joins specify how the SQL Server can utilize the data from one data table to base the selection of rows from another data table. This is done based on values common to both tables. Typically this is used when the tables have a one-to-many mapping or a many-to-one mapping to identify relationships between them. 

There are two aspects in the join operation in SQL to specify how tables are related:

• Column name: The join function requires the column from each of the tables to be specified. Typically this is a foreign key- primary key association between the tables. 
• Logical operator: The join function in SQL can be accompanied by a logical operator such as ‘equal to (=)’, ‘less than (<)’, or ‘greater than (>)’ to compare values from the specified columns.

Next, let us understand the difference between joins in SQL. 

2. Types of Joins in SQL

Often we are faced with situations where we need to know how to join two tables in SQL or need to join multiple tables in SQL to retrieve relevant data. For this reason, there are various types of joins in SQL which are followed by specific clauses. The clauses are created based on the logical relation between the tables that are being used. This is possible in a relational database setup. 

To understand the different types of joins in SQL, let us consider a situation where we need to extract data from two tables with a matching column- Table A and Table B with ‘Table A’, being the first table specified in the join syntax and ‘Table B’ being the second one. 

• Inner join

Also called ‘Simple Join’, this type of join retrieves rows which has matching data in both specified tables. This is the default join in SQL.

• Left outer join 

Also called ‘Left Join’, applying this type of join retrieves all rows from Table A even if there is no data that matches in Table B.

• Right outer join

Also called ‘Right Join’, applying this type of join retrieves all rows from Table B even if there is no data that matches in Table A

• Full outer join

Also called ‘Full Join’, applying this type of join retrieves rows if there is a match in at least one of the tables, A or B. 

• Cross join

Also called ‘Cartesian Join’, applying this join gives the Cartesian product of the rows in the specified tables. This means that a ‘Cross Join’ will retrieve a table that has rows that combine each row of Table A with each row of Table B.

• Self Join

Apart from the different joins in SQL, we have reviewed, in special cases, a table can be joined to itself. This usually requires the table to be referred to by an alias (a temporary name) at least once in the SQL statement. The operation is then executed as if performed on two different tables.

3. Use of Join in SQL

Data is spread across multiple tables in a relational database. The join concept in SQL becomes necessary when you need to get a meaningful set of data in such a database. Thus, for all joins in SQL, the clause that follows the join operator specifies the relation based on which the data need to be extracted from the tables. 

Inner join function can be mentioned in either the ‘FROM’ and ‘WHERE’ clauses. However, Outer Join as well as Cross Join can be specified only in the ‘FROM’ clause. Do try using all the different types of joins in SQL with examples on a database. 

For example, let us consider a database that stores Employee information and Department information in two different tables- ‘Employee’ and ‘Department’. There could be a scenario where:

1. We need the list of employees and the department to which they belong
2. if the Employee and that mentioned in ‘Department’ do not match. 

Both these situations can be tackled with the help of joins. We can simply join 2 tables in SQL to extract the rows using a ‘WHERE’ clause that specifies: 

1. Employee ID in Employee = EmployeeID in Department
2. Employee ID in Employee <>EmployeeID in Department

Similarly, we can find a relevant application in real life for every type of join. 

How to join multiple tables

There may be instances when you need a combination of data from more the two tables. But any type of join can be applied to two tablets at a time. So, for SQL to join multiple tables with conditions, any number of tables can be joined by embedding one JOIN clause within another. 

Do keep in mind that:

·The join conditions, the ‘WHERE’ clause, and ‘HAVING’ conditions all come together to identify and control the rows selected from the table mentioned in the ‘FROM’ clause. 

• Many a time, other search conditions are usually mentioned in the ‘WHERE’ clause. To detach the join condition from these, it is recommended that the join condition in SQL is specified in the ‘FROM’ clause. 

We touched upon join conditions with only two tables. However, when dealing with data in real life, we will need to collect information in more than just two tables. It is therefore imperative that the database is designed and presented well. This could be by use of good naming conventions or by following rules that ensure minimal overlapping of data collected. All these are critical when we attempt to join multiple tables.

Conclusion

By now you have understood how join works in SQL, how many types of joins in SQL, and how we can utilize them to extract data in a relational database. To further your understanding, we encourage you to form queries based on table relations to extract specific rows. Start with a simple SQL query to join two tables. Next, attempt to join 4 tables in SQL and then progress to more complex ones!

If you are interested in making it big in the world of data and evolve as a Future Leader, you may consider our Integrated Program in Business Analytics, a 10-month online program, in collaboration with IIM Indore!

Also, Read

What is SQL: Comprehensive Beginner’s Guide