Joins in SQL with Example Study Material Notes for Beginners
JOINS
Joins are needed to retrieve data from two tables’ related rows on the basis of some condition which satisfies both the tables. Mandatory conditions to join is that at-least one set of column (s) should be taking values from same domain in each table.
Types of Joins
The Two types of joins are given below.
Inner Join
inner join is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. These may be further divided into three parts.
- Equi Join (satisfies equality condition)
- Non-Equi Join (satisfies non-equality condition)
- Self Join (one or more column assumes the same domain of values) Considers only pairs that satisfy the joining condition
Result set of T1 and T2.
.
Key Points
- The cross join does not apply any predicate to filter records from the joined table. Programmers can further filter the results of a cross join by using a WHERE clause.
- An equi-join is a specific type of comparative-based join, that uses only equality comparisons in the join-predicate.
- A special case, a table (base table, view, or joined table) can JOIN to itself in a self-join.
Outer Join
An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record-even if no other matching record exists.
Considers also the rows from table (S) even if they don’t satisfy the joining condition.
- Right outer join
- Left outer join
- Full outer join
Left Outer Join
The result of a left outer join for table A and B always contains all records of the left table (A), even if the join condition does not find any matching record in the right table (B).
Right Outer Join
A right outer closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the right table will appear in the joined table at least once. If no matching with left table exists, NULL will appear.
Result set of T1 and T2
| T1-ID | Name | T2-ID | Branch |
| 1 | Ram | 1 | IT |
| NULL | NULL | 3 | cs |
Full Outer Join
A full outer join combines the effect of applying both left and right outer joins. Where records in the FULL OUTER JOIN table do not match, the result set will have NULL values for every column of the table that lacks a matching row. For those records that do match, as single row will be produced in the result set.
Cross Join (Cartesian Product)
Cross join returns the Cartesian product of rows form tables in the join. lt will produce rows which combine each row from the first table with each row from the second table.
Select * FROM T1, T2
Number of rows in result set = (Number of rows in table 1 x Number of rows in table 2)
Result set of T1 and T2 (Using previous tables T1 and T2)
Key Points
- A programmer writes a JOIN predicate to identify the record for joining if the evaluated predicate is true, the combined record is then expected format a record set or temporary table.
- The right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate) .
- A left outer join returns all the values from an inner join plus all values in the table that do not match to the right table.
Sorting in Design and Analysis of Algorithm Study Notes with Example
Learn Sorting in Handbook Series: Click here
Follow Us on Social Platforms to get Updated : twiter, facebook, Google Plus