Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation

Answer: Using INNER JOIN

Explore how to use the INNER JOIN SQL command to retrieve matching records from two related tables. Understand the use of aliases for clarity, and learn alternate join methods with filtering to handle various data retrieval scenarios.

We'll cover the following...

Solution

The solution is given below:

MySQL
/* The query to apply INNER JOIN */
SELECT     e.EmpName, p.ProjectName 
FROM       Employees AS e 
INNER JOIN Projects AS p ON e.EmpID = p.EmpID;

Explanation

The explanation of the solution code is given below:

  • Line 2: The SELECT query selects EmpName and ProjectName. The e.EmpName refers to the EmpName column from the Employees table (aliased as e), and p.ProjectName refers to the ProjectName column from the Projects table (aliased as p).

  • Line 3: The data is retrieved from the Employees table.

  • Line 4: The INNER JOIN is applied with Employees and Projects on the EmpID column in both the tables.

Recall of relevant concepts

We have covered the following concepts in this question:

  • Selective columns

  • Aliases

  • Using INNER JOIN

Let’s discuss the concepts used in the solution:

  • We specify the columns to retrieve in the SELECT query; it retrieves the records on the basis of specified conditions. The FROM clause is used to specify the table from which we want to retrieve the data. 

SELECT Column1, Column2, Columnn
FROM   TableName;

  • Aliases are used to give a temporary name to tables or columns. An alias only exists for the duration of the query. Usually, the AS keyword is used for specifying an alias. However, it can also be omitted. Examples are as follows:

/* Column alias */
SELECT ColumnName AS ColAlias
FROM   TableName;
/* Column alias with space */
SELECT ColumnName AS "Col Alias"
FROM   TableName;
/* Table alias */
SELECT t.ColumnName
FROM   TableName AS t;
/* Table alias without using AS */
SELECT t.ColumnName
FROM   TableName t;
Syntax for column and table aliases in SQL

  • SQL joins are used to combine rows from two or more tables based on their related columns. An INNER JOIN SQL operation returns records that have matching values in both tables.

SELECT     a.ColumnName
FROM       TableName1 AS a 
INNER JOIN TableName2 AS b ON a.CommonColumn = b.CommonColumn;

  • INNER JOIN can also be applied by specifying columns in the USING clause as follows:

SELECT     a.ColumnName
FROM       TableName1 AS a 
INNER JOIN TableName2 AS b USING (CommonColumn);

Alternate solutions

Let’s discuss the alternate solutions for the same problem in this section:

USING with INNER JOIN

We can use the USING keyword with a JOIN. We can specify the name of a common column with the USING keyword. Let’s have a look at the following query:

MySQL
SELECT     e.EmpName, p.ProjectName 
FROM       Employees AS e 
INNER JOIN Projects AS p USING (EmpID);

LEFT JOIN and WHERE

We can use a LEFT JOIN SQL command with a WHERE clause for filtering. We can use LEFT JOIN to connect two tables, and for the NULL values, we filter the records using the WHERE clause. Let’s have a look at the following query:

MySQL
SELECT    e.EmpName, p.ProjectName
FROM      Employees AS e
LEFT JOIN Projects AS p ON e.EmpID = p.EmpID
WHERE     p.ProjectName IS NOT NULL;

RIGHT JOIN and WHERE

We can use RIGHT JOIN with a WHERE clause for filtering. We can use RIGHT JOIN to connect two tables, and for the NULL values, we filter the records using the WHERE clause. Let’s have a look at the following query:

MySQL
SELECT     e.EmpName, p.ProjectName
FROM       Employees AS e
RIGHT JOIN Projects AS p ON e.EmpID = p.EmpID
WHERE      e.EmpName IS NOT NULL;

USING with LEFT JOIN and WHERE

We can use LEFT JOIN with a WHERE clause for filtering. We can use LEFT JOIN to connect two tables, and for the NULL values, we filter the records using the WHERE clause. We can specify the name of a common column with the USING keyword. Let’s have a look at the following query:

MySQL
SELECT    e.EmpName, p.ProjectName
FROM      Employees AS e
LEFT JOIN Projects AS p USING (EmpID)
WHERE     p.ProjectName IS NOT NULL;

USING with RIGHT JOIN and WHERE

We can use RIGHT JOIN with a WHERE clause for filtering. We can use RIGHT JOIN to connect two tables, and for the NULL values, we filter the records using the WHERE clause. We can specify the name of a common column with the USING keyword. Let’s have a look at the following query:

MySQL
SELECT     e.EmpName, p.ProjectName
FROM       Employees AS e
RIGHT JOIN Projects AS p USING (EmpID)
WHERE      e.EmpName IS NOT NULL;

The WHERE condition

We can use the WHERE clause for filtering. We just need to list the column names in the SELECT statement, the table names in the FROM clause, and the condition in the WHERE clause. Let’s have a look at the following query:

MySQL
SELECT e.EmpName, p.ProjectName
FROM   Employees AS e, Projects AS p
WHERE  e.EmpID = p.EmpID;

Similar interview questions

Let’s discuss the variations of the same problem in this section:

Purpose of the INNER JOIN in SQL

Attempt the following quiz to find out if you understand the workings of INNER JOIN.

Technical Quiz
1.

What is the purpose of an INNER JOIN in SQL?

A.

To retrieve all rows from both tables, combining them into one result set

B.

To retrieve only the rows from both tables that have matching rows in the other table

C.

To retrieve only rows from the left table that have matching rows in the right table

D.

To retrieve only rows from the right table that have matching rows in the left table

1 / 1

Find the employees whose skills are not in the database

Attempt the following quiz to know if you’re able to find the employees whose skills are not recorded.

Technical Quiz
1.

Considering the following query, which is the correct option for finding the name of the employees whose skills are not in the database?

SELECT e.EmpName FROM Employees AS e WHERE
...
A.
e.EmpID NOT IN (SELECT s.EmpID FROM Skills AS s INNER JOIN Employees AS e ON s.EmpID = e.EmpID);
B.
EmpID = NULL;
C.
e.EmpID IN (SELECT s.EmpID FROM Skills AS s INNER JOIN Employees AS e ON s.EmpID = e.EmpID);
D.
e.EmpID = NULL;
1 / 1

Brain teaser

Try to solve the following puzzle to find the employees who have a salary less than the salary of Alexa Smith:

If you’re unsure how to do this, click the “Show Solution” button.

Key terms

We have covered the following key terms in the lesson:

SELECT

FROM

WHERE

INNER JOIN

AS

ON

RIGHT JOIN

LEFT JOIN

IS NOT NULL