MySQL - INNER JOIN Tables - Syntax and Examples - ON vs USING vs WHERE

An INNER JOIN only returns data for which there is a match between both tables being joined. INNER JOIN syntax compares rows of Table1 with Table2 to check whether there is a match based on join condition(s) specified in the ON clause. When there is a match for INNER JOIN condition(s), it returns matched rows from both tables with the columns listed in the SELECT clause.

INNER JOIN Syntax

This is the most common syntax of INNER JOIN I have seen when working with numerous SQL databases over the years.

INNER JOIN With Explicit INNER Keyword

SELECT column_name(s)
FROM Table1
INNER JOIN Table2
ON Table1.column_name = Table2.column_name;

INNER is the default type of JOIN used by MySQL database engine so you can omit the INNER keyword if the intent is to perform an INNER JOIN between 2 tables.

INNER JOIN Omitting INNER Keyword

SELECT column_name(s)
FROM Table1
JOIN Table2
ON Table1.column_name = Table2.column_name;

Sometimes you might see people performing INNER JOIN using only WHERE clause like below.

INNER JOIN With WHERE Clause

SELECT column_name(s)
FROM Table1, Table2
WHERE Table1.column_name = Table2.column_name;

When both sides of the join use the same name for the joining column(s), some people utilize the USING clause as a shorthand to perform INNER JOIN.

INNER JOIN With USING Clause

SELECT column_name(s)
FROM Table1
INNER JOIN Table2
USING (column_name(s));

INNER JOIN Example Data

---

We will illustrate INNER JOIN concept using the example of 2 related tables that store coders along with their coding practices.

Coder table stores coders. CoderID is the primary key of the table that can uniquely identify a coder. CorderName contains a coder name provided by the user.

Coder table

CREATE TABLE Coder
  (
     CoderID   INT NOT NULL,
     CoderName VARCHAR(300) NOT NULL,
     PRIMARY KEY (CoderID)
  );

INSERT INTO Coder (CoderID,CoderName) VALUES (1, 'Alice');
INSERT INTO Coder (CoderID,CoderName) VALUES (2, 'Dave');
INSERT INTO Coder (CoderID,CoderName) VALUES (3, 'Bob');
INSERT INTO Coder (CoderID,CoderName) VALUES (4, 'Emily');
INSERT INTO Coder (CoderID,CoderName) VALUES (5, 'Sam');

Coder	CoderName
1	Alice
2	Dave
3	Bob
4	Emily
5	Sam

Practice table stores coding practices of each coder. PracticeID is the primary key of the table that can uniquely identify a coding practice. CoderID is a foreign key that references to the CoderID column of the Coder table. PracticeDate records the date of a coding practice.

Practice table

CREATE TABLE Practice
  (
     PracticeID   INT NOT NULL,
     CoderID      INT,
     PracticeDate DATE,
     PRIMARY KEY (PracticeID),
     FOREIGN KEY (CoderID) REFERENCES Coder(CoderID)
  );

INSERT INTO Practice (PracticeID,CoderID,PracticeDate) VALUES (1001, 1, '2022-01-18');
INSERT INTO Practice (PracticeID,CoderID,PracticeDate) VALUES (1002, 1, '2022-01-19');
INSERT INTO Practice (PracticeID,CoderID,PracticeDate) VALUES (1003, 2, '2022-01-18');
INSERT INTO Practice (PracticeID,CoderID,PracticeDate) VALUES (1004, 3, '2022-01-18');

PracticeID	CoderID	PracticeDate
1001	1	2022-01-18
1002	1	2022-01-19
1003	2	2022-01-18
1004	3	2022-01-18

Use

DB-Fiddle link to execute SQL scripts on sample data.

INNER JOIN with ON Clause

This is the INNER JOIN query that returns coders who have completed at least 1 coding practice.

INNER JOIN Coder and Practice With INNER Keyword

SELECT *
FROM Coder
INNER JOIN Practice
ON Coder.CoderID = Practice.CoderID

caution

Note that we need to prefix CoderID column with table name so the database engine can distinguish between these columns in two separate tables.

Let’s walk through this INNER JOIN query line by line.

• The first line uses the SELECT keyword to indicate we want to return all columns from both tables.
• The second line specifies the Coder table as the first table we want to include.
• The third line specifies the additional table Practice we want to include in our query with INNER JOIN keyword.
• The fourth line specifies an INNER JOIN condition with the ON clause. In this case, we are connecting the CoderID column of the Coder table (Coder.CoderID) to the CoderID column of the Practice table (Practice.CoderID).
• Finally, the query will return matched rows from both tables with all columns from both table. Note that CoderID columns from both tables show up in the results.

CoderID	CoderName	PracticeID	CoderID	PracticeDate
1	Alice	1001	1	2022-01-18
1	Alice	1002	1	2022-01-19
2	Dave	1003	2	2022-01-18
3	Bob	1004	3	2022-01-18

JOIN with ON Clause

info

Note you can omit INNER keyword as the default JOIN type in MySQL database engine is INNER, but I recommend explicitly specifying the JOIN type for the sake of clarity.

INNER JOIN Coder and Practice Tables Omitting INNER Keyword

SELECT *
FROM Coder
JOIN Practice
ON Coder.CoderID = Practice.CoderID

CoderID	CoderName	PracticeID	CoderID	PracticeDate
1	Alice	1001	1	2022-01-18
1	Alice	1002	1	2022-01-19
2	Dave	1003	2	2022-01-18
3	Bob	1004	3	2022-01-18

JOIN Tables With WHERE Clause

Sometimes you might come cross other people's codes that perform INNER JOIN between tables using only WHERE clause. Even though the following query would produce the same results, it's usually not recommended in places that have coding standards in place.

INNER JOIN Coder and Practice Tables with Only WHERE Clause

SELECT *
FROM Coder
	,Practice
WHERE Coder.CoderID = Practice.CoderID

CoderID	CoderName	PracticeID	CoderID	PracticeDate
1	Alice	1001	1	2022-01-18
1	Alice	1002	1	2022-01-19
2	Dave	1003	2	2022-01-18
3	Bob	1004	3	2022-01-18

info

The advantage of the INNER JOIN and ON keywords is that they explicitly present the logic of the join and indicate relationship(s) between tables.

INNER JOIN with USING Clause

The USING clause is a shorthand allowing user to take advantage of the specific situation where both sides of the join use the same name (in our case CoderID) for the joining column(s). USING clause can take a comma-separated list of the shared column names and forms a join condition that includes an equality comparison for each column listed.

info

An additional bonus for USING is that we no longer have to fully qualify the joining column(s) when they have the same name (in our case CoderID).

SELECT *
FROM Coder
INNER JOIN Practice
USING (CoderID)

info

Note that when SELECT all columns from both tables, the joining column (in our case CoderID) will display in the result set twice with ON while it appears only once with USING clause.

CoderID	CoderName	PracticeID	PracticeDate
1	Alice	1001	2022-01-18
1	Alice	1002	2022-01-19
2	Dave	1003	2022-01-18
3	Bob	1004	2022-01-18