SQL server executes the SQLs and returns the result set. The result is the set of rows that are retrieved from the table. There may be a need to retrieve the data from more than one table. There must be one common attribute in the tables to perform the operation.

The connecting column from both the tables should have some common values. The values should be of similar data type.

The general syntax for join is as shown below:

Consider the two tables: studentdetails and studentresult. The tables are as shown below:

studentdetails

studentresult

The join condition for the two tables is as shown below:

The output table is as shown below:

9.1 Using an Inner join

The inner join command is used to combine data from more than 1 table. The records are retrieved from multiple tables after comparing the common column. When the inner join is applied, only the rows with values satisfying the join condition are displayed.

Consider the tables:  empstatus and salary.

empstatus

salary

The query to display the data using the inner join is as shown below:

The output for the join query is as shown below:

9.2 Using an Outer join

The outer join is used to select the data from the table irrespective of the data from the second table. The result set is containing all the rows from one table and the matching rows from the other table.

The syntax for the outer join is as shown below:

The outer join is of the following types:

1) Left Outer Join

2) Right Outer Join

3) Full Outer Join

1) Left Outer Join

The left outer join returns the rows from the table specified on the left side of the LEFT OUTER JOIN keyword and all the matching rows from the specified table on the right side. It displays the NULL for the columns of the table specified on the right side when there are no matching records.

Consider the tables: empstatus and empholiday.

empstatus

empholiday1

The query to display the left outer join is as shown below:

The output for the table is as shown below:

2) Right Outer Join

The right outer join returns the rows from the table specified on the right hand side of the RIGHT OUTER JOIN keyword and the matching rows from the table specified on the left side.

Consider the tables: empstatus and empholiday1.

empstatus

empholiday1

Consider the following query for the right outer join.

The output of the query is as shown below:

3) Full Outer Join

The full outer join is the combination of left and right outer join. This join returns all the matching and non matching rows from both the tables. The similar records are displayed only once. The NULL value is returned for the columns for which the data is unavailable.

The query for full outer join is applied to the empstatus and empholiday1 table. The sql statement is as shown below:

The output of the query is as shown below:

9.3 Using a cross join

The cross join is known as Cartesian Product. It combines each row of one table with each row of another table. The number of rows in the result set is equal to the number of rows in the first table multiplied by the number of rows in the second table.

The syntax for the cross join is as shown below:

Consider the example of Department and Employee table.

Department

Employee

The cross join query for the table is as shown below:

The output for the query is as shown below:

9.4 Using an equi join

An equi join is same as an inner join table. The equality operator is used to specify the join condition, whereas the conditional operators are used to specify the join condition in an inner join.

The syntax for the equi join is as shown below:

Consider the table as UserDetails and OrderDetials.

UserDetails

OrderDetails

The query for displaying the equi join data is as shown below:

The output of the query is as shown below:

9.5 Using a self join

The self join is a table joined with itself. One row of the table correlates with the other rows in the same table. The table name is used twice in the query. To differentiate between the two tables, the table name is given an alias name.

The syntax for the self join is as shown below:

Consider the Student table.

The query for the table is as shown below:

The output of the code is as shown below: