SQL Join:




The SQL join is an important component of a relational database that exists between two tables. The SQL join allows the user to tie the data existing in one table to the data in another table.

The SQL join type relationships are useful when the user wants to query the related data from more than one table and then wants to retrieve that data in a much more meaningful way. This is because to create the relationships between the tables purposeful.




The SQL join operation is an operation that is used to match rows in one table with the rows in another table. The purpose for this is that the columns from both the tables can be paced side by side as if the columns are from one table in a query result.

To perform the “join” operation in SQL the join keyword is used that joins two or more tables or a minimum of (n-1) tables and n here is the number of tables to be joined. In SQL we can also join the table to itself and this property is called SELF JOIN.

There are a number of types of join operation that are defined by the SQL. The SQL implementation decides that which type of “join” operation the user should use and also what the data the user want returned and how the tables are defined.

 

Types of Join

Following are the types of JOIN operation in SQL:

  1. Inner
  2. Outer
  3. Left
  4. Right

 

Basic Join Operations

 

Comma Separated Join

The comma separated join is the simplest type of join to implement in SQL. In the comma separated the user has to supply only the list of the tables that is separated by a comma. The list of tables is mentioned in the FROM clause of the select statement. Suppose you have the following two tables STUDENT and STUDENT RESULT.

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

STUDENT

SUBJECT_ID STUD_ID Subject Marks
204 100 English 56
230 100 Math 78
144 140 Computer 89
230 110 Math 84
204 110 English 68
144 190 Computer 94
204 150 English 64
144 150 Computer 76

STUDENT RESULT

Let’s suppose that you want to show the data from the table STUDENT and the table STUDENT RESULT. The user can view the data from the tables STUDENT and STUDENT RESULT via querying each of the table separately. The user can also view the data from the tables by joining the tables in one single statement.

The query to join two tables STUDENT and STUDENT RESULT by the comma separated join is as the following one using the SELECT statement:

SELECT *FROM STUDENT, STUDENT RESULT;

 

Cartesian Product Table

The Cartesian product also known as cross join is used to return the Cartesian product of the results of the set of records from more than two joined tables. The Cartesian product is the list of each of the rows in one table that are joined together with each of the rows in the other table. Therefore, the result that is obtained by using the Cartesian product or cross join is a table consisting of rows from one table combined with the rows in another table. The syntax of the cross join or Cartesian product is given below:

SYNTAX

SELECT table1.column-name1, table2.column-name2…

FROM table-name1

CROSS JOIN

Table-name2;

The difference between the cross join and comma separated join is that in a comma separated join we separate the column names by a comma and in the cross join we simply used the cross join keyword.

EXAMPLE of CROSS JOIN

Suppose you have the following two tables STUDENT and STUDENT-RESULT:

STUD_ID NAME DEPT MONFEE
100 Tim Marketing 1000
100 Faddy Marketing 1000
140 Jones Accounting 1200
110 John Info. System 1100
110 James Info. System 1100

STUDENT

SUBJECT_ID Subject Marks
204 English 56
230 Math 78
144 Computer 89
230 Math 84
204 English 68

STUDENT-RESULT

The cross join query for the above table is given below:

SELECT * FROM STUDENT,

CROSS JOIN STUDENT-RESULT;

The result generated by this query will be as follows:

STUD_ID NAME DEPT MONFEE SUBJECT_ID Subject Marks
100 Tim Marketing 1000 204 English 56
100 Faddy Marketing 1000 204 English 56
140 Jones Accounting 1200 204 English 56
110 John Info. System 1100 204 English 56
110 James Info. System 1100 204 English 56
100 Tim Marketing 1000 230 Math 78
100 Faddy Marketing 1000 230 Math 78
140 Jones Accounting 1200 230 Math 78
110 John Info. System 1100 230 Math 78
110 James Info. System 1100 230 Math 78
100 Tim Marketing 1000 144 Computer 89
100 Faddy Marketing 1000 144 Computer 89
140 Jones Accounting 1200 144 Computer 89
110 John Info. System 1100 144 Computer 89
150 Stuart Marketing 1000 144 Computer 89
100 Tim Marketing 1000 230 Math 84
100 Faddy Marketing 1000 230 Math 84
140 Jones Accounting 1200 230 Math 84
110 John Info. System 1100 230 Math 84
110 James Info. System 1100 230 Math 84
100 Tim Marketing 1000 204 English 68
100 Faddy Marketing 1000 204 English 68
140 Jones Accounting 1200 204 English 68
110 John Info. System 1100 204 English 68
110 James Info. System 1100 204 English 68

 

INNER Join or EQUI Join

The inner join or the EQUI join is based on a condition that is used with the equal sign or we can also say that the EQUI join or inner join is based on an equality condition. This equality condition is specified in the query. The syntax of the INNER join is as follows:

SYNTAX

SELECT column-name

From table-name

INNER JOIN

Table-name2

Where table-name1.column-name = table-name2.column-name;

EXAMPLE of INNER Join

Suppose you have the following two tables STUDENT and STUDENT-RESULT:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

STUDENT

SUBJECT_ID Subject Marks
100 English 56
100 Math 78
140 Computer 89
110 Math 84
110 English 68
190 Computer 94
204 English 64
144 Computer 76

STUDENT-RESULT

Use the following query to understand the concept of inner join:

SELECT *FROM STUDENT, STUDENT_RESULT where STUDENT.STUD_ID = STUDENT-RESULT.SUBJECT_ID;

The following result will be generated by this query:

STUD_ID NAME DEPT MONFEE CRSNO CDTE Subject Marks
100 Tim Marketing 1000 SPSS 19/06/96 English 56
100 Faddy Marketing 1000 SURVEYS 10/07/96 Math 78
140 Jones Accounting 1200 TAXACCT 12/08/97 Computer 89
110 John Info. System 1100 SPSS 14/07/96 Math 84
110 James Info. System 1100 COBOL 22/07/96 English 68
190 Abraham Finance 1200 INVESTMENT 20/06/97 Computer 94




Natural Join

The natural join is used because it automatically matches the rows for the columns that have the same name. The user does not need to specify any condition for the natural join. The decision that which of the columns have the same name is taken by the SQL implementation.

And then the SQL implementation forms a match. The natural join can also be referred as the type of inner join. The natural join is column based the columns that have the same data type. This data type is determined by examining both of the tables. The syntax of natural join is as follows:

SYNTAX

SELECT * from table-name1

NATURAL JOIN

Table-name2;

EXAMPLE of Natural Join

Suppose you have the following two tables STUDENT and STUDENT-RESULT:

STUD_ID NAME DEPT MONFEE CRSNO CDTE
100 Tim Marketing 1000 SPSS 19/06/96
100 Faddy Marketing 1000 SURVEYS 10/07/96
140 Jones Accounting 1200 TAXACCT 12/08/97
110 John Info. System 1100 SPSS 14/07/96
110 James Info. System 1100 COBOL 22/07/96
190 Abraham Finance 1200 INVESTMENT 20/06/97
150 Louis Marketing 1000 SPSS 19/06/97
150 Stuart Marketing 1000 SYSANAL 20/07/97

STUDENT

STUD_ID Subject Marks
100 English 56
100 Math 78
140 Computer 89
110 Math 84
110 English 68
190 Computer 94
150 English 64
150 Computer 76

STUDENT-RESULT

Use the following query to understand the concept of natural join:

SELECT * from STUDENT

Natural Join

STUDENT-RESULT;

The result generated by the above query will be as follows:

STUD_ID NAME DEPT MONFEE CRSNO CDTE Subject Marks
100 Tim Marketing 1000 SPSS 19/06/96 English 56
100 Faddy Marketing 1000 SURVEYS 10/07/96 Math 78
140 Jones Accounting 1200 TAXACCT 12/08/97 Computer 89
110 John Info. System 1100 SPSS 14/07/96 Math 84
110 James Info. System 1100 COBOL 22/07/96 English 68
190 Abraham Finance 1200 INVESTMENT 20/06/97 Computer 94
150 Louis Marketing 1000 SPSS 19/06/97 English 64
150 Stuart Marketing 1000 SYSANAL 20/07/97 Computer 76

In the above example, both of the tables STUDENT and STUDENT-RESULT were having an identical column that is the data type and the name of the column was same therefore, when the natural join is used the result is the records that have the same column name and same column data type.

 

Outer Join

The only difference between an inner join and the outer join is that the inner join returns the records that meet certain conditions defined in the SELECT statement. In other words we can say that the inner join is used to return only the matched rows. On the other hand, the outer join is used to return matched rows as well as some or even all the unmatched rows. This depends on the type of the outer join that is being used. The following are the sub categories of an outer join:

  1. Left outer join
  2. Right outer join
  3. Full outer join

 

Left outer join

For the left outer join LEFT keyword is used. The left outer join is used to indicate that the table on the left side of the join determines the number of rows in the result. And the table on the right side is used so that it can provide the column values whenever the match is found. The syntax of left outer join is as follows:

SYNTAX

SELECT column-name-list

From table-name1

LEFT OUTER JOIN

Table-name2

On table-name-1.column-name = table-name2.column-name;

In the above syntax the ON clause is used. The ON clause is used to join unlimited number of tables.

EXAMPLE of Left Outer Join

Suppose you have the following tables INSTRUCTOR and STUDENT:

Dept_No DEPT Instructor-ID Instructor-Name
10 Marketing E01 John Abraham
20 Management E02 Tim
30 Finance E04 Ali
40 Accounting E11 Faddy
50 IT E25 Kate

Instructor

STUD_ID NAME Dept_No DEPT
100 Stuart 10 Marketing
100 Bills 20 Management
140 Jones 30 Finance
110 Johnny 40 Accounting
110 James 60 IT

Student

Use the following query for left outer join.

SELECT *from Instructor LEFT OUTER JOIN student ON (Instructor. Dept_No = Student. DEPT)

The above query will generate the following result:

Dept_No DEPT Instructor-ID Instructor-Name STUD_ID NAME Dept_No DEPT
10 Marketing E01 John Abraham 100 Stuart 10 Marketing
20 Management E02 Tim 100 Bills 20 Management
30 Finance E04 Ali 140 Jones 30 Finance
40 Accounting E11 Faddy 110 Johnny 40 Accounting
50 IT E25 Kate NULL NULL NULL NULL

In the above example the Dept_No of the instructor and the dept number of student table are compared if they matched then all the other entities are displayed of the student table and if they are not matched then NULL is displayed to the right side.

 

Right Outer Join

The right outer join is used to return all the rows that are matched and all the rows that are not matched from the table to the right side of the JOIN keyword. It returns null for the table that is on the left side of the JOIN keyword. It can be observed that the left outer join returned null for the table that was on the right side of the table. And right outer join is returning null for the table that is on the left side of join keyword. The syntax of right join is given below:

SYNTAX

SELECT column-name-list

From table1

RIGHT OUTER JOIN

Table2

ON table1.column-name = table2.column-name;

EXAMPLE of Right Outer Join

Suppose you have the following two tables Instructor and Student:

Dept_No DEPT Instructor-ID Instructor-Name
10 Marketing E01 John Abraham
20 Management E02 Tim
30 Finance E04 Ali
40 Accounting E11 Faddy
50 IT E25 Kate

Instructor

STUD_ID NAME Dept_No DEPT
100 Stuart 10 Marketing
100 Bills 20 Management
140 Jones 30 Finance
110 Johnny 40 Accounting
110 James 60 IT

Student

Now use the following query to understand the concept of right outer join:

SELECT * From Instructor Right Outer Join Student ON (Instructor. Dept_No = Student. Dept_No);

The above query will generate the following result:

Dept_No DEPT Instructor-ID Instructor-Name STUD_ID NAME Dept_No DEPT
10 Marketing E01 John Abraham 100 Stuart 10 Marketing
20 Management E02 Tim 100 Bills 20 Management
30 Finance E04 Ali 140 Jones 30 Finance
40 Accounting E11 Faddy 110 Johnny 40 Accounting
NULL NULL NULL NULL 110 James 60 IT

In the above example the Dept_No of the instructor and the dept number of student table are compared if they matched then all the other entities are displayed of the student table and if they are not matched then NULL is displayed to the left side.




Full Outer Join

The full outer join is used to return all the rows from both of the tables either they are matched or not. If the data is matched of the two tables then the data will be displayed and if the data of right table is not matched with the data of left table then NULL will be displayed on the right side and vice versa. The syntax of full outer join is as follows:

SYNTAX

SELECT column-name-list

From table1

FULL OUTER JOIN

Table2

ON table1.column-name = table2.column-name;

EXAMPLE of Full Outer Join

Suppose you have the following two tables Instructor and Student:

Dept_No DEPT Instructor-ID Instructor-Name
10 Marketing E01 John Abraham
20 Management E02 Tim
30 Finance E04 Ali
40 Accounting E11 Faddy
50 IT E25 Kate
80 Software E40 Kathy

Instructor

STUD_ID NAME Dept_No DEPT
100 Stuart 10 Marketing
100 Bills 20 Management
140 Jones 30 Finance
110 Johnny 40 Accounting
110 James 60 IT
120 Sam 70 Computer

Student

Now use the following query to understand the concept of full outer join:

SELECT * From Instructor Full Outer Join Student ON (Instructor. Dept_No = Student. Dept_No);

The above query will generate the following result:

Dept_No DEPT Instructor-ID Instructor-Name STUD_ID NAME Dept_No DEPT
10 Marketing E01 John Abraham 100 Stuart 10 Marketing
20 Management E02 Tim 100 Bills 20 Management
30 Finance E04 Ali 140 Jones 30 Finance
40 Accounting E11 Faddy 110 Johnny 40 Accounting
50 IT E25 Kate NULL NULL NULL NULL
80 Software E40 Kathy NULL NULL NULL NULL
NULL NULL NULL NULL 110 James 60 IT
NULL NULL NULL NULL 120 Sam 70 Computer

It can be seen in the above example that NULL is displayed on the right side if the data on the right table does not match with the data on the left table. And NULL is displayed on the left side if the data on the left does not match with the data on the right table.