SQL Joins – A Simple Guide

If you are working with SQL relational databases, at some point you are going to use JOINs. Normalized data is organized and spread across multiple tables, oftentimes linked by a common column key (or multiple keys). We use JOINs to bring the data together. Therefore JOINs are an important concept to grasp. There are SQL JOIN articles and guides across the internet far and wide and I am adding one more into the mix…

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

I am using Oracle SQL for the example JOIN queries in this post. The SQL implementation you are using should have comparable JOINs. However, all SQL databases are not exactly the same so be sure and consult the documentation for your vendor for any specific questions or clarification.

The example queries for this post are derived from the 2 tables shown below. They are both super simple in structure and design, each containing a single NUMBER column named NUM_COL with INTEGER values:

SELECT *
FROM NUM1_TAB;
SELECT *
FROM NUM2_TAB;

SQL JOINs: Categories of JOINs

There are essentially 2 types or categories of JOINs:

  • INNER JOIN – An INNER JOIN returns combined rows from the tables involved, only where there is a match between the data values found in the tables.
  • OUTER JOIN – An OUTER JOIN returns rows involved between the target tables whether there is a match or not between the data.

SQL JOINs: NATURAL JOIN

A NATURAL JOIN performs the JOIN on any same-named columns between the target tables. By default, a NATURAL JOIN is an INNER JOIN but can be used with OUTER JOINs as well:

SELECT
    NUM_COL, NUM_COL
FROM
    NUM1_TAB N1
NATURAL JOIN
    NUM2_TAB N2;
oracle-sql-natoracle-sql-natural-join
Query results from a NATURAL JOIN.

Since this NATURAL JOIN query is an INNER JOIN type, only matching rows are returned. Both tables NUM1_TAB and NUM2_TAB have rows with column values of 5 and 6, which are returned.

(Tip: Unlike other JOIN types, table alias prefixes cannot be used on the actual join columns in a NATURAL JOIN. In our example, neither of the 2 NUM_COL column names in the SELECT list has a table alias prefix.)

SQL JOINs: USING Keyword

The USING keyword also joins table rows based on identically named columns found across the tables involved in the JOIN. USING works for both INNER and OUTER JOIN types.

USING syntax is:

USING(column_name)

Since both target tables have a column named NUM_COL, we can specify this column in parentheses after the USING keyword to perform the JOIN on:

SELECT
    NUM_COL, NUM_COL
FROM
    NUM1_TAB N1
JOIN
    NUM2_TAB N2
USING (NUM_COL);
oracle-sql-using
Query results from the USING keyword.

Again since this query is an INNER JOIN type, only matching rows are returned as in the NATURAL JOIN example.

(Tip: Same as with NATURAL JOIN, no table alias prefix can be used on the join column when the USING keyword is used.)

SQL JOINs: INNER JOIN

One of the most common types of JOIN‘s you will use or encounter is the INNER JOIN. While there is a place for OUTER JOINs, and oftentimes an OUTER JOIN is the only way to get the data you need, INNER JOIN‘s are frequently used. The INNER JOIN uses an explicit join condition via the ON keyword.

Where in the example queries with NATURAL JOIN and the USING keyword, the join condition was somewhat taken care of for us, with an INNER JOIN, we must specify it ourselves.

SELECT
    N1.NUM_COL, N2.NUM_COL
FROM
    NUM1_TAB N1
INNER JOIN
    NUM2_TAB N2
ON N1.NUM_COL = N2.NUM_COL;

(Tip: The INNER keyword is optional and can be omitted altogether.)

ooracle-sql-inner-join-results
Query results from an INNER JOIN.

Identical query results are returned in this example query. However, the join condition is specified on the line:

ON N1.NUM_COL = N2.NUM_COL;

SQL JOINs: Column Ambiguity

In the previous examples covering NATURAL JOIN and the USING keyword, table alias column prefixes were not allowed in the query. But, for an INNER JOIN with identically named columns, without them, we will get an error for ambiguity. No matter if they are omitted in the ON join condition or the SELECT clause list:

--ambiguity error !!!
SELECT
    NUM_COL, NUM_COL
FROM
    NUM1_TAB N1
INNER JOIN
    NUM2_TAB N2
ON N1.NUM_COL = N2.NUM_COL;
--ambiguity error !!!
--ambiguity error !!!
SELECT
    N1.NUM_COL, N2.NUM_COL
FROM
    NUM1_TAB N1
INNER JOIN
    NUM2_TAB N2
ON NUM_COL = NUM_COL;
--ambiguity error !!!

Since both tables involved in the INNER JOIN have a column named NUM_COL and it is included in the SELECT list, we must distinguish them in the query by qualifying with an alias prefix. Table alias prefixes on the columns fix this ambiguity issue.

Code, content, and community for developers.

The LAMP stack and the PHP technologies and frameworks it runs.

SQL JOINS’s: LEFT OUTER JOIN

The JOINs we have used so far return results where there is a match between the rows of data in the target join tables. Now It is time to learn about OUTER JOINs. As mentioned earlier in the post, an OUTER JOIN returns both matching and non-matching rows from the target JOIN tables.

But, what does that look like?

We start with a LEFT OUTER JOIN. In the following example query, notice that the NUM1_TAB and NUM2_TAB tables are listed in the FROM clause, separated by the LEFT JOIN keywords (the OUTER keyword is optional):

SELECT
    N1.NUM_COL, N2.NUM_COL
FROM
    NUM1_TAB N1
LEFT JOIN
    NUM2_TAB N2
ON N1.NUM_COL = N2.NUM_COL;

All rows are returned from table NUM1_TAB. Also, matching rows column values 5 and 6 in NUM2_TAB are returned. However, where there is no match for the NUM_COL column from the NUM2_TAB table, NULL is returned. Recall the NUM2_TAB table has numeric values in the range of 5 – 10 where the NUM1_TAB table contains range values 1 – 6.

Therefore technically, the only rows that match in both tables are those with column values of 5 and 6.

SQL JOINs: RIGHT OUTER JOIN

The converse of the LEFT OUTER JOIN is the RIGHT OUTER JOIN. In this type of JOIN, all rows are returned from the table specified by the RIGHT JOIN keywords. The first table listed in the FROM clause (in this example query NUM1_TAB) returns any matching rows, however, where there is no match, NULL is returned for these rows:

SELECT
    N1.NUM_COL, N2.NUM_COL
FROM
    NUM1_TAB N1
RIGHT JOIN
    NUM2_TAB N2
ON N1.NUM_COL = N2.NUM_COL;

SQL JOINs: FULL OUTER JOIN

If we want to combine the functionality we get from both a LEFT JOIN and RIGHT JOIN, we can use the FULL OUTER JOIN.

SELECT
    N1.NUM_COL, N2.NUM_COL
FROM
    NUM1_TAB N1
FULL OUTER JOIN
    NUM2_TAB N2
ON N1.NUM_COL = N2.NUM_COL;

(Tip: Like the other OUTER JOINs, the OUTER keyword is optional for the FULL JOIN.)

We can ORDER BY one of the table columns and place a sorting on the result set to further see and understand the underlying operations.

SELECT
    N1.NUM_COL, N2.NUM_COL
FROM
    NUM1_TAB N1
FULL OUTER JOIN
    NUM2_TAB N2
ON N1.NUM_COL = N2.NUM_COL
ORDER BY N1.NUM_COL ASC;

In the FULL JOIN query results, matching and non-matching rows are returned for both tables’ data. For the non-matching rows, NULL is returned. We can clearly see that row column values of 5 and 6 are the only rows in common between both tables.

SQL JOIN’s: CROSS JOIN

The last JOIN we will cover is the CROSS JOIN. CROSS JOINs return the Cartesian product of the JOIN table rows. There is no ON join specification or USING keyword in a CROSS JOIN.

To demonstrate a CROSS JOIN, I’ll use the 2 tables below.

For the CROSS JOIN, each row in TAB1 will be joined with each row in TAB2. Since there are 5 rows in TAB1 and 3 rows in TAB2, 15 rows are returned in the CROSS JOIN query below:

SELECT *
FROM TAB1
CROSS JOIN TAB2;

As always, if you have any questions or see any mistakes in the code, please let me know via the comments. Constructive comments help me provide accurate blog posts and are much appreciated.

Like what you have read? See anything incorrect? Please comment below and thank you for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog posts/technical writing I have completed for clients.



To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

One thought on “SQL Joins – A Simple Guide”

  1. Pingback: CodeIgniter 4 Query Builder join() Method Explained - Digital Owl's Prose

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.