How Does One Choose Between the SQL Right Join Inner vs. Outer in SQL?

An SQL Join is an operation of accessing the data from two or more tables or relations. In simple terms, a join occurs whenever multiple tables or relations appear in the FROM clause of a query, as described by Bhanuprakash, Nijagunarya & Jayaram (2014).

Any number of attributes or columns can be chosen in the SELECT clause. When two tables are joined, one must find the standard columns from both tables to join the tables with a relational operator. If a column has the same name in both tables, it must be qualified by the table name to avoid the uncertainty of which table the column’s data should be retrieved.

There are two types of joins. They are the Inner Join and Outer Join. The inner join, which can also be referred to as the simple join, is a join of two or more tables or relations that return only the records that satisfy the join condition. In other words, the inner join is looking for the columns joined with matching tuples or rows from both tables, as described by Bhanuprakash, Nijagunarya & Jayaram (2014).

An outer join extends the result of the inner join.

The outer join, as described by Bhanuprakash, Nijagunarya & Jayaram (2014), returns all tuples or rows of a join condition and returns some or all of the tuples or rows from one table for which no rows or tuples from the other table satisfy the join condition. Another way to say this would be looking for matching rows or tuples and the remaining rows or tuples from one table. If the remaining rows or tuples are retrieved out of the table on the left or right, it is a left outer join or a right outer join and if data is retrieved from both tables it is a full outer join.

The best place to use inner and outer joins is in a database design where one can take advantage of relationships built into the database design. These queries would be used to return data sets based on relationships built in the database design.

The SQL joins allow the users to query or take advantage of relationships or primary and foreign keys. Taking advantage of these types of SQL queries is one of the powers of relational databases and why they are still so popular in the Information Technology World.

References

Bhanuprakash, C., Nijagunarya, Y. S., & Jayaram, M. A. (2014). A simple approach to SQL joins in a

relational algebraic notation. International Journal of Computer Applications, 104(4)

doi:http://dx.doi.org.ezproxy1.apus.edu/10.5120/18190-9099