Author: mahdy M7244
When we are talking about relational database, we are usually talking about many different tables and the relationships between them. We define relationships with various keys like primary key, foreign key, etc. When we have to retrieve data from more than one table, we have to use Joins. In this article we will be discussing how we can code joins effectively and retrieve data from more than one table.
The JOIN keyword is used in a SQL statement to query data from two or more tables based on a relationship between certain columns in these tables.
In this blog post we discuss about following joins.
- Inner Join
- Outer Join
- Left Outer Join
- Right Outer Join
- Full Outer Join
A JOIN that displays only rows that have a match in both the JOINed tables is known as inner JOIN. This is the default type of JOIN in the Query and View Designer.
A JOIN that includes rows even if they do not have related rows in the joined table is an Outer JOIN. You can create three different outer JOINs to specify the unmatched rows to be included:
Left Outer JOIN
In Left Outer JOIN, all rows in the first-named table, i.e., the “left” table, which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear.
Right Outer JOIN
In Right Outer JOIN, all rows in the second-named table, i.e., the “right” table, which appears rightmost in the JOIN clause, are included. Unmatched rows in the left table are not included.
Full Outer JOIN
In Full Outer JOIN, all rows in all the joined tables are included, whether they are matched or not.
With the help of Venn diagrams we can more easily understand how joins work. Besides Inner Join and Outer Join, other kinds of joins are possible (cross join, natural joins, etc.). However, the most frequently used joins are inner and outer join which are explained in this article.