Introduction
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.
This article applies to various relational databases like SQL Server, MySQL, PostgreSQL, Oracle and others.
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
Inner 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.
Outer JOIN
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.
Conclusion
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.
Next Steps
For more SQL tips, download our ebook, SQL Server in Simple Words, by Pinal Dave. Learn more about Embarcadero Rapid SQL, the intelligent IDE for SQL development, and try Rapid SQL for free.