INNER JOIN returns only common records from both tables (left table and right table) if the records do not match then will not show. INNER JOIN is used between two tables.
For SELECT Statement
Syntax:-
SELECT table_name1.column_name(s), table_name2.column_name(s)
FROM table_name1
INNER JOIN
table_name2
ON table_name1.column_name=table2.column_name
Example:- Suppose we have two table employees and emp_salary.
employees Table:- this table has the employee’s details.
+----+------------+-----------+----------------+-----------+
| id | first_name | last_name | email | country |
+----+------------+-----------+----------------+-----------+
| 1 | John | Tailor | john@abc.com | USA |
| 2 | Rom | Tailor | rom@abc.com | USA |
| 3 | Andrew | Symonds | andrew@abc.com | Australia |
| 4 | Miacle | Tailor | miacle@abc.com | Australia |
| 5 | Sachin | Tendulkar | sachin@abc.com | India |
| 6 | Virat | Kohli | virat@abc.com | India |
| 7 | rohit | NULL | rohit@abc.com | India |
emp_salary table:- this table employee’s salary details.
| id | emp_id | salary |
+----+--------+--------+
| 1 | 5 | 200000 |
| 2 | 6 | 180000 |
| 3 | 8 | 300000 |
| 4 | 9 | 400000 |
+----+--------+--------+
Find the employees who have salary.
Query:-
SELECT employees.id,employees.first_name,employees.last_name,
employees.email,employees.country,emp_salary.salary
FROM employees
INNER JOIN
emp_salary
On employees.id=emp_salary.emp_id
Output:-
+----+------------+-----------+----------------+---------+--------+
| id | first_name | last_name | email | country | salary |
+----+------------+-----------+----------------+---------+--------+
| 5 | Sachin | Tendulkar | sachin@abc.com | India | 200000 |
| 6 | Virat | Kohli | virat@abc.com | India | 180000 |
+----+------------+-----------+----------------+---------+--------+
SQL INNER JOIN – Interview Questions
Q 1: What is INNER JOIN?
Ans: Returns only matching records.
Q 2: Is INNER JOIN default JOIN?
Ans: Yes
Q 3: Does INNER JOIN return NULLs?
Ans: Only if stored.
Q 4: Can INNER JOIN join multiple tables?
Ans: Yes
Q 5: Is INNER JOIN fast?
Ans: Yes, with proper indexing.
SQL INNER JOIN – Objective Questions (MCQs)
Q1. What does an INNER JOIN return?
Q2. INNER JOIN is the default join type when using:
Q3. Which condition is required for INNER JOIN?
Q4. INNER JOIN excludes:
Q5. INNER JOIN is best used when: