Explore topic-wise InterviewSolutions in .

Another type of join in SQL is a SELF JOIN, which connects a table to itself. In order to perform a self-join, it is necessary to have at least one column (say X) that serves as the primary key as well as one column (say Y) that contains values that can be matched with those in X. The value of Column Y may be null in some rows, and Column X need not have the exact same value as Column Y for every row.

Example: Consider the table Employees.

For instance, we might wish to display results that only include employees with their managers. By using table aliases and a self-join, this can be accomplished easily. 

Output:

At times, you might need to retrieve data from three or more tables at once. A multi-table join requires consecutive JOIN operations: the first and second tables are joined to form a virtual table and then the third table is joined to this virtual table. Let's take a look at three tables. 

Here’s the Employee table.

Here's the Employment table. 

Here’s the EmpDetail.

Output:

A JOIN is one of the mechanisms that we use to combine the data of more than one table in a relational database, and a Nested Join is one of the simplest methods involving the physical joining of two tables. In essence, a Nested Join uses one joining table as an outer input table while the other one serves as an inner input table. With a Nested Loop Join, one row from the outer table is retrieved and then the row is searched for in the inner table; this process is repeated until all the output rows from the outer table have been searched for in the inner table. Nested Loop Join may further be sub-categorized into Indexed Nested, Naive Nested and Temporary Index Nested Loop Join. 

Just like any other join, the hash join requires two inputs, which are the probe input (inner table) and the build input (outer table). A hash join involves the use of a hash table to identify rows matching between two tables. The hash join is the option when no other join is preferred (possibly due to the absence of sorting or indexing etc). Hash joins are best when joining large data sets that are unsorted and non-indexed.

• 
  Left Join: It returns datasets that have matching records in both tables (left and right) plus non-matching rows from the left table. By using a left join, all the records in the left table plus the matching records in the right table are returned.

Example: Let's take a look at two tables. Here’s the Tb1_Employee table.

Here's the Tb2_Employment table. 

Let’s now perform LEFT JOIN on these two tables using a SELECT statement, as shown below: 

Output:

• 
  Right Join: It returns datasets that have matching records in both tables (left and right) plus non-matching rows from the right table. By using a left join, all the records in the right table plus the matching records in the left table are returned.

Example: Let’s now perform RIGHT JOIN on these two tables using a SELECT statement, as shown below: 

Output:

• 
  Inner Join: This join generates datasets that contain matching records in both tables (left and right). By using an inner join, only the rows that match between each of the tables are returned; all non-matching rows are removed.

Example: Let's take a look at two tables. Here’s the Tb1_Employee table.

Here's the Tb2_Employment table.

Let’s perform INNER JOIN on these two tables using a SELECT statement, as shown below: 

Output: 

• 
  Left Join: It returns datasets that have matching records in both tables (left and right) plus non-matching rows from the left table. By using a left join, all the records in the left table plus the matching records in the right table are returned.

Example: Let’s now perform LEFT JOIN on these two tables using a SELECT statement, as shown below: 

Output:

Merge join produces a single output stream resulting from the joining of two sorted datasets using an INNER, FULL, or LEFT join. It is the most effective of all the operators for joining data. Specifically, merge join requires that both inputs be sorted as well as matching meta-data in the joined columns. Users can't join columns of different data types together. Users are not permitted to combine a column with a numeric data type with a column with a character data type.

There are various types of join statements you can use depending on the use case you have. Specifically, there are four types of joins as follows:

• 
  Inner Join: This method returns datasets that have the same values in both tables.


• 
  Full Join: This is also referred to as a full outer join. It combines all rows from the left table and the right table to create the result set. It means that a query would return records from both tables, even if they had NULL values. If there are no matching rows, the result-set (joined table) will display NULL values.


• 
  Right Join: This is also referred to as the Right outer join. All records from the right table will be returned, as well as any records that match from the left table.


• 
  Left Join: This is also referred to as Left outer join. All records from the left table will be returned, as well as any records that match from the right table.

SQL joins are important in database management for the following reasons:

• A method of stitching a database back together to make it easier to read and use.


• Additionally, they maintain a normalized database. Data normalization helps us keep data redundancy low so that when we delete or update a record, we will have fewer data anomalies in our application.


• Joins have the advantage of being faster, and as a result, are more efficient.


• It is almost always faster to retrieve the data using a join query rather than one that uses a subquery.


• By utilizing joins, it is possible to reduce the workload on the database. For example, instead of multiple queries, you can use one join query. So, you can better utilize the database's ability to search, filter, sort, etc.

A join clause is a SQL command used to combine records from multiple tables or retrieve data from these tables based on the existence of a common field (column) between them. A join condition and SELECT statement can be used to join the tables. Using the SQL JOIN clause, records can be fetched from two or more tables in a database and combined. In general, they are used when users need to retrieve data from tables that contain many-to-many or one-to-many relationships between them.

Example: Let's take a look at two tables. Here’s the Employee table.

Here's the Employment table. 

Let us now join these two tables together using a SELECT statement, as shown below.

Output: