SQL Joins

Download PDF
Advertisement

SQL Joins

SQL Joins: A joins is a relational operation that combines row in one table to the rows in another table related table according to common values existing in corresponding columns such as primary and foreign key columns.

The joins condition is specified with WHERE clause of SELECT statement. You may join two tables if each one contains a column with same domain of values. It must be noted that to join multiple tables, there should be one WHERE condition for each pair of tables being joined. Thus if N tables are to be combined, there should be N-1 WHERE condition.

Join is the combination of product, projection and selection operations. If selection operation is omitted, then the resultant table may contain duplicated rows.

Types of SQL Joins

There are the following types of joins

SQL Equi-Joins

Equi-joins is the type of join in which joining condition is based on the equality (=) between values in common columns.

Advertisement

For example, to retrieve data from STUDENT and RESULT table the SELECT statement is written as:

SELECT Student.Roll_no,Student.Name,Student.City,Result.Roll_no,Result.Marks

FROM Student,Result

WHERE Student.Roll_no = Result.Roll_no

The result of the above query is

Student.Roll_no Name City Result.Roll_no Marks
1 Ali Lahore 1 895
2 Hamza Multan 2 664

SQL Non-Equi Join

If the condition is based on other comparison operators other than “=” operator such as <, >, >=, <=, <>, AND, OR, NOT and BETWEEN etc. This type of joining is called non-equi joins.

SQL Self Join

Sometimes you need to join a table to itself. A type of join in which a table is joined to itself is called Self Join. The columns of the table on which the join is created must have the same domain.

SQL Outer Join

In equi-join and natural join, only the matching rows of both tables are added into the result that satisfy the join condition. Sometimes, it is the requirement of the user or organization that all the rows of one table or both tables should be included in the resultant table. For the purpose, the outer join is used. Therefore the outer join is defined as

“ A join in which matching rows as well as non-matching rows of one side table or both side table are also included in the query result, is called Outer Join.

Outer Join is further divided into two types.

SQL Left Outer Join

Left outer join adds every row of the first table in the result, if no matching tuple is found in the second table. The attributes included in the query result of the second table are filled with null values.

SQL Right Outer Join

Right Outer Join add every row of the second table in the result, if no matching tuple is found in the first table, then  the attributes of the first table are filled with null values.

 

Structure Query Language

Advertisement