For managing data stored in an RDBMS or stream processing in a relational data stream management system, programmers use the domain-specific language SQL (Structured Query Language). Among many useful features of SQL lies SQL Joins. By matching the values that are shared by both tables, a JOIN allows you to combine fields from two tables. We have the Joins in SQL to implement joins for combining data from two or more tables in a database.
So, in this blog, we intend to throw light on Joins in SQL, their types, and some other important features related to SQL Joins. For better understanding and clarity of the concepts, you would be getting examples in each topic covered under SQL Joins.
Table of contents
SQL Joins
One of the many interesting operations that we can perform through SQL is Joins. SQL Joins allow us to combine records from two or more tables of a database based on a shared attribute or column. We have the JOIN clause for implementing Joins in SQL or applying the join query in SQL. There are four different kinds of Joins in SQL, and we will discuss each of them along with their syntaxes and examples.
For scenarios where the relationship between two tables shows a kind of hierarchy, we can use JOIN to fetch data from both the tables and, further, observe the relationship. For example, if there is a table containing all the customer records and another table containing the order records. Since the relationship between both the tables is that customers place their orders. Hence, we can see the order details of certain customers by joining the two tables.
Also Read- SQL Functions: Aggregate and Scalar Functions with Examples
The syntax of using the JOIN clause in SQL is given as-
Syntax:
SELECT *COLUMN_NAMES* FROM TABLE1 JOIN TABLE2ON TABLE1.COMMON_COL =TABLE2.COMMOM_COL
NOTE: By default, the JOIN clause carries out the inner join operation on the tables.
Take a look at the following two tables we will use here for citing the examples in case of each join in SQL.
Table 1: Teacher Table
| TEACHER_ID | TACHER_NAME | SUBJECT | CONTACT |
| 1014 | RANJANA KHANNA | ENGLISH | 9535455433 |
| 1012 | SONAM MAURYA | MATHEMATICS | 8736653542 |
| 1021 | VISHAL PATHAK | HINDI | 9865356277 |
| 1015 | MANJULATA SINGH | ECONOMICS | 8736363721 |
| 1013 | MRINALINI SINGH | PHYSICS | 7865634243 |
| 1026 | PREETI RAHEJA | CHEMISTRY | 9735662523 |
| 1019 | MRIDUL BAJPAYEE | ACCOUNTS | 7892125688 |
| 1020 | MADHU SONKER | BIOLOGY | 9563819230 |
Table 2: Department Table
| DPT_ID | SUBJECT | HEAD |
| 501 | PHYSICS | NAINA BANSAL |
| 502 | CHEMISTRY | ANKUR SINHA |
| 503 | MATHS | SONAM MAURYA |
| 504 | ENGLISH | KIRAN JOSHI |
| 505 | HINDI | VISHAL PATHAK |
| 506 | ECONOMICS | KIRAN SRIVASTAVA |
| 507 | HISTORY | MEENAKSHI PAL |
| 508 | GEOGRAPHY | SHRAVAN SINGH |
| 509 | CIVICS | LALITA BHARDWAJ |
| 510 | ACCOUNTS | NIKHIL MATHEWS |
| 511 | BIOLOGY | BEENA BASU |
| 512 | LINGUISTIC | MANISHA DIXIT |
Now, let us try performing the Join query in SQL on the above two tables as per the given syntax.
Example:
SELECT * FROM DEPARTMENTJOIN TEACHERON DEPARTMENT.SUBJECT = TEACHER.SUBJECT;
Output:
NOTE: We can directly write the desired column names in the FROM clause as long as there is no common column in the list.
Now that you have gotten a rough idea of what Joins in SQL is and how a join query in SQL works, let us dive deeper into different types of SQL joins.
Types of SQL Joins
As mentioned before, there are mainly four types of joins in SQL. The four types of SQL joins have been enlisted below:
- Inner Join
- Left Join
- Right Join
- Full Join
We will now discuss each of these joins one by one, starting from Inner Join in SQL up to Right Join.
Inner Join
Inner join is used for selecting all the rows out of 2 or more tables depending on the fulfillment of the matching column condition. We have the INNER JOIN clause in SQL for implementing an inner join on two tables. It generates a result set that consists of all the rows from both the tables where the value for the common field is the same in both the tables. The syntax for writing an INNER JOIN query in SQL is below:
Syntax:
SELECT * FROM table_1 INNER JOIN table_2ON table_1.common_column = table_2.common_column; Or SELECT table_1.column_1, table_1.column_2, …, table_2.column_1, table_2.column_2,…FROM table_1 INNER JOIN table_2ON table_1.common_column = table_2.common_column;
Here, two syntaxes have been given. The first one is applied when you want all the fields from both tables. On the other hand, the latter is used when we want data from selective fields of each table. Since the JOIN keyword, in general, is used for an inner join in SQL, we can drop the word INNER and use JOIN alone whenever we are required to implement an inner join in SQL.
Example 1:
Now, let us try running an INNER JOIN query on the two tables – the TEACHER table and the DEPARTMENT table.
SELECT * FROM DEPARTMENTINNER JOIN TEACHERON DEPARTMENT.SUBJECT = TEACHER.SUBJECT;
Output:
Example 2:
SELECT TEACHER.TEACHER_NAME, DEPARTMENT.DPT_ID, TEACHER.SUBJECT FROM TEACHERINNER JOIN DEPARTMENTON DEPARTMENT.SUBJECT = TEACHER.SUBJECT;
Output:
As you can see from the output snaps, using * instead of column names, give us all the columns from both the tables. In the other case, we have selected the TEACHER_NAME field from the TEACHER table, the DPT_ID field from the DEPARTMENT table, and the SUBJECT field from either of the tables. The selected rows get displayed in the order they are used in the SELECT statement.
Left Join
In a left join, the rows that match the table on the right side of the join are returned along with all of the rows from the table on the left side of the join. We have the LEFT JOIN clause for carrying out a left join on two tables in SQL. The result-set will include null for all rows of the left side table for which there is no matching row on the right side. LEFT OUTER JOIN is another name for LEFT JOIN. Let us look at its syntax given below.
Syntax:
SELECT * FROM table_1 LEFT JOIN table_2ON table_1.common_column = table_2.common_column; Or SELECT table_1.column_1, table_1.column_2, …, table_2.column_1, table_2.column_2,…FROM table_1 LEFT JOIN table_2ON table_1.common_column = table_2.common_column;
The first syntax applies the LEFT JOIN query on all the tables’ columns, whereas the second is for selecting specific columns from both tables. Since left join and left outer join both refer to the same thing, we can also use the LEFT OUTER JOIN clause in place of the LEFT JOIN clause.
Take a look at the following examples for a better understanding of left join in SQL.
Example 1:
SELECT * FROM TEACHERLEFT JOIN DEPARTMENTON DEPARTMENT.SUBJECT = TEACHER.SUBJECT;
Output:
Here, as we have given the TEACHER table on the left-hand side of the LEFT JOIN clause, all the records from that table have been fetched. There are no corresponding department details for the teacher SONAM MAURYA, so the DEPARTMENT table fields are left empty (null) for the TEACHER_NAME SONAM MAURYA.
Let’s now try putting the DEPARTMENT table on the left side of the LEFT JOIN clause.
Example 2:
SELECT * FROM DEPARTMENTLEFT JOIN TEACHERON DEPARTMENT.SUBJECT = TEACHER.SUBJECT;
Output:
No, as you can see from the output snap, all the records from the DEPARTMENT table have been shown. And, the DEPARTMENT table records for which there are no respective TEACHER table records have the NULL value, which is also shown with a vacant cell in some SQL editors.
Example 3:
SELECT DEPARTMENT.DPT_ID, TEACHER.TEACHER_ID, DEPARTMENT.SUBJECTFROM DEPARTMENT LEFT JOIN TEACHERON DEPARTMENT.SUBJECT = TEACHER.SUBJECT;
Output:
So, in this example, we have DEPARTMENT is on the left side of the LEFT JOIN clause, and we have written DEPARTMENT.SUBJECT in the SELECT clause. Hence all its records of DPT_ID and SUBJECT have been displayed in the output of the query. Now, if you notice the TEACHER table’s SUBJECT column does not have so many entries, but since we have selected the SUBJECT from the DEPARTMENT table, all the subjects are present in the DEPARTMENT table can be seen in the result set.
Here, in this example, we have used TEACHER.SUBJECT instead of DEPARTMENT.SUBJECT with the SELECT clause. Consequently, there are some empty cells or cells with a NULL value in the resultant SUBJECT field, which has been fetched from the TEACHER table.
Right Join
Right join is just the opposite of left join direction-wise. We have the RIGHT JOIN clause for performing a right join operation in SQL. When we want to have all the records from the table on the right side of the RIGHT JOIN clause, we simply use the right join in SQL. For the rows from the right table that do not have corresponding values in the left table, the left table’s fields for those records are blank or null. The syntax for using a RIGHT JOIN clause in SQL has been given below.
Syntax:
SELECT * FROM table_1 RIGHT JOIN table_2ON table_1.common_column = table_2.common_column; Or SELECT table_1.column_1, table_1.column_2, …, table_2.column_1, table_2.column_2,…FROM table_1 RIGHT JOIN table_2ON table_1.common_column = table_2.common_column;
The resultant set of a right join may look like a left join with swapped positions of tables in the join query. Let us understand this better with the help of examples.
Example 1:
SELECT * FROM DEPARTMENTRIGHT JOIN TEACHERON DEPARTMENT.SUBJECT = TEACHER.SUBJECT;
Output:
As you can see, the DPT_ID and HEAD columns of the DEPARTMENT table have NULL values for the TEACHER_ID 1012. This is because all the records from the right table, i.e., the TEACHER table, have been included. This result set is similar to the result of left join with swapped positions for the tables (Example-1 of left join).
Example 2:
SELECT * FROM TEACHERRIGHT JOIN DEPARTMENTON TEACHER.SUBJECT = DEPARTMENT.SUBJECT;
Output:
Since the DEPARTMENT table is the right table, all the records of the table have been displayed. For the rows of the DEPARTMENT table that have no matching records in the TEACHER table, the TEACHER table columns show NULL values.
Example 3
SELECT TEACHER.TEACHER_ID, DEPARTMENT.DPT_ID, TEACHER.SUBJECTFROM DEPARTMENT RIGHT JOIN TEACHERON DEPARTMENT.SUBJECT = TEACHER.SUBJECT;
Output:
Now that you have understood how a RIGHT join query in SQL works, you know how the above output is generated. Let’s try running just one more example of SQL RIGHT JOIN query where only some of the columns from both the tables are being selected, and the DEPARTMENT table is taken as the right one.
Example 4
SELECT TEACHER.TEACHER_ID, DEPARTMENT.DPT_ID, DEPARTMENT.SUBJECTFROM TEACHER RIGHT JOIN DEPARTMENTON DEPARTMENT.SUBJECT = TEACHER.SUBJECT;
Output:
In this example, since the SUBJECT column from the DEPARTMENT table has been selected, which is on the right side, there are no NULL values. Had we selected the SUBJECT column from the TEACHER table, there would have been 5 NULL values corresponding to the TEACHER_ID with NULL in the resultant set.
Full Join
When we want to integrate the results of both LEFT JOIN and RIGHT JOIN to produce the result set, we use full join. For implementing a full join in SQL, we have the FULL JOIN clause on two tables. All the rows from both tables are included in the result set. The result-set will contain NULL values for the rows where there was no match.
The syntax for a FULL JOIN in SQL is given as follows:
SELECT * FROM table_1 FULL JOIN table_2ON table_1.common_column = table_2.common_column; Or SELECT table_1.column_1, table_1.column_2, …, table_2.column_1, table_2.column_2,…FROM table_1 FULL JOIN table_2ON table_1.common_column = table_2.common_column;
For clarity, let us try running the FULL JOIN query on the two tables, TEACHER and DEPARTMENT.
Example 1:
SELECT * FROM DEPARTMENTFULL JOIN TEACHERON DEPARTMENT.SUBJECT = TEACHER.SUBJECT
Output:
As we can see from the output of the above query, FULL JOIN merges the LEFT and RIGHT joins, thereby producing a result-set that consists of all the rows from both tables. For all the missing values of fields corresponding to the rows of either of the tables, it gives NULL in the output-set.
Example 2:
SELECT TEACHER.TEACHER_ID, DEPARTMENT.DPT_ID, TEACHER.SUBJECT FROM DEPARTMENTFULL JOIN TEACHERON DEPARTMENT.SUBJECT = TEACHER.SUBJECT
Output:
In this example, as inferred from the output, all the records from the TEACHER and DEPARTMENT table have been making the result set. But since we have written TEACHER.SUBJECT instead of DEPARTMENT.SUBJECT, only the subjects for which TEACHER record is given have been displayed, and the rest of the SUBJECT cells show NULL values.
The other names of Full Join are Full Outer Join and Outer Join. We can perform the same function as we perform using the FULL JOIN clause with the help of the OUTER JOIN clause as well.
SQL Joins and Aliases
SQL allows us to give temporary names to the tables or even columns of a table for our convenience. This property is known as aliasing. We sometimes have numerous queries to write, and we struggle with long table or column names while writing queries. We can allot temporary names to the desired tables and columns in such situations. For example, if we are writing long Join queries in SQL, we have to write the table name and the column name integrated with a dot operator for selecting the columns. As such, we can create table and column aliases to make query writing less tedious.
Since aliases created are temporary, they last as long as the query runs. We use the AS clause to generate table and column aliases. The syntax for making table and column aliases has been given below.
Syntax for Table Alias
SELECT column1, column2, column3, …FROM table_name AS alias_name;
Syntax for Column Alias
SELECT column1 AS alias_name1, column2 AS alias_name2FROM table_name;
Example OF Table Alias:
SELECT DPT_ID, SUBJECT, HEADFROM DEPARTMENT AS DPT;
Output:
Example OF Column Alias:
SELECT TEACHER_ID AS ID, CONTACT AS PHONEFROM TEACHER;
Output:
We can also use both the table and column aliases in a single query. We often make use of aliases while writing Join queries in SQL. Aliases make JOIN queries in SQL easy to read and write. We use the following syntax for the same.
Syntax of Join with Alias:
SELECT table1_alias.column1 AS column1_alias, table2_alias.column2_alias, …..FROM table1 AS table1_aliasJOIN table2 AS table2_aliasON table1_alias.matching_column = table2_alias.matching_column
Example of Join with Alias:
SELECT TCH.TEACHER_ID AS TID, DT.DPT_ID AS DID FROM DEPARTMENT AS DTOUTER JOIN TEACHER AS TCHON DT.SUBJECT = TCH.SUBJECT
Output:
As you can see in the above example, we have used column and table aliases in a single query of JOIN. We have selected the column TEACHER_ID from TEACHER and named it TID, DPT_ID from DEPARTMENT, and named it DID. Now, we have also used two aliases for the two tables, one for each – TCH is the alias name for table TEACHER, and DT is the alias name for the DEPARTMENT table.
As seen from the above query, using table and column aliases doesn’t make us undergo all the hassle of writing long table and column names. The query has become more readable. The output set shows the column aliases instead of the original column names. Hence, it is important to use relatable names for the aliases to avoid any kind of confusion.
The benefits of using aliases are as follows:
- Writing queries where we are needed to write table names or column names twice or more times becomes time-saving and less tedious
- Aliases are used to increase the readability of the query.
- Aliases can be used for providing convenient names to the tables or columns in a query
- Making use of table aliases enables the query to become less confusing.
- Column aliases can be used to generate a rather suitable name for a column in a query.
- JOIN queries become less lengthy using table aliases.
Examples
So far, you have learned what joins are, their different types and how the Joins in SQL work. You have also seen how the aliases work in SQL and how they can be used with JOINs. As mentioned in the former part of this blog, Joins in SQL can be used on more than two tables, i.e., three tables as well. Let’s go through a bunch of examples that will help you strengthen your concepts of joins and aliases.
For example, let us create one more table, PROGRAM:
Example 1: JOINING DEPARTMENT AND PROGRAM WHERE DPT_ID AND DPT_NAME ARE THE MATCHING COLUMNS
SELECT * FROM DEPARTMENTJOIN PROGRAMON DEPARTMENT.DPT_ID = PROGRAM.DPT_NAME;
Output:
Example 2: JOINING THREE TABLES – TEACHER, DEPARTMENT, AND PROGRAM
SELECT TEACHER_ID, TEACHER.TEACHER_NAME, FUNC_NAMEFROM DEPARTMENT D JOIN PROGRAM PON D.DPT_ID = P.DPT_NAME JOIN TEACHERON D.SUBJECT = TEACHER.SUBJECT;
Output:
The TEACHER table has no direct connection with the PROGRAM table as there are no matching columns. But, both the TEACHER table as well as the PROGRAM table are connected to the DEPARTMENT table through the SUBJECT column and DPT_ID column, respectively.
Another interesting way of aliasing has been shown through this example. Instead of using the AS clause, we have directly given temporary names to the tables DEPARTMENT and PROGRAM as D and P, respectively, for the sake of writing a query.
It was necessary to attach the table name along with the TEACHER_NAME column as the PROGRAM table also has the TEACHER_NAME column, and it could give rise to ambiguity. Since TEACHER_ID and FUNC_NAME are two different columns that solely belong to TEACHER and PROGRAM tables, we need not write table names along with these columns.
Example 3: JOIN ON TEACHER AND DEPARTMENT BY MATCHING THE TEACHER_NAME AND HEAD COLUMN
SELECT TEACHER_ID, T.TEACHER_NAME, DPT_ID, D.SUBJECTFROM TEACHER AS T JOIN DEPARTMENT AS DON T.TEACHER_NAME = D.HEAD;
Output:
Here, only those records from both the tables have been shown for which the value of TEACHER_NAME field is equal to the value of the HEAD field.
Example 3: JOIN THE THREE TABLES AND USE WHERE CONDITION
SELECT TEACHER_ID, T.TEACHER_NAME, FUNC_NAMEFROM DEPARTMENT D JOIN PROGRAM PON D.DPT_ID = P.DPT_NAME JOIN TEACHER TON D.SUBJECT = T.SUBJECTWHERE T.TEACHER_NAME LIKE "%A";
Apart from using the JOIN and aliases, we have also filtered the result set using a constraint. We have used the WHERE clause to filter out the records from the three tables to ensure the teacher’s name ends with “A”.
Likewise, we can apply several other constraints on the resultant set of a JOIN operation in SQL. We can also use GROUP BY and ORDER BY clauses along with a JOIN clause the way we do with any other SQL clause.
Conclusion
So, this is pretty much all about the Joins in SQL. You can get your hands on free SQL courses to upskill yourself with the concept and earn a completion certificate. Happy Learning!
