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.
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.
