SQL joins explained: INNER, LEFT, RIGHT, FULL and self join

What is a SQL join, in one line?#

You are sitting across a whiteboard in a Siruseri interview room. Two boxes. Employees. Departments. The interviewer taps the marker and waits. This is still the question. Not window functions. Not recursive queries. Joins. A join is SQL's way of stitching two tables together using a matching column. That is the one-line version.

Think of a Flipkart order page. The order id sits in one table. The customer's name sits in another. The page cannot print "Priya ordered a charger" unless those tables meet. That meeting is a join.

We split the data on purpose. Repeat "Engineering, Floor 6" on every employee row and the table turns into mush. Store the department once. Store people once. Join them when a screen needs both.

One sentence for the interview: joins exist so normalised tables can still answer a question that spans more than one table.

How do INNER join and LEFT join actually differ?#

Draw two circles. Employees on the left. Departments on the right. The overlap is people who have a department that actually exists.

INNER JOIN is only that overlap. No match, no row. The intern with a NULL dept_id is gone. A Finance team with nobody in it is also gone. You asked for matches. SQL gave you matches.

WITH departments AS (
  SELECT 1 AS id, 'Engineering' AS name
  UNION ALL SELECT 2, 'Support'
  UNION ALL SELECT 3, 'Finance'
),
employees AS (
  SELECT 1 AS id, 'Priya' AS name, 1 AS dept_id
  UNION ALL SELECT 2, 'Karthik', 1
  UNION ALL SELECT 3, 'Meena', 2
  UNION ALL SELECT 4, 'Arun', NULL
)
SELECT e.name AS employee, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

Arun has no department in this data. INNER JOIN drops him. Priya, Karthik and Meena stay.

LEFT JOIN treats the left table as sacred. Every employee comes back. Missing department? The department columns are NULL. You still see Arun. You just see a blank next to his name.

WITH departments AS (
  SELECT 1 AS id, 'Engineering' AS name
  UNION ALL SELECT 2, 'Support'
  UNION ALL SELECT 3, 'Finance'
),
employees AS (
  SELECT 1 AS id, 'Priya' AS name, 1 AS dept_id
  UNION ALL SELECT 2, 'Karthik', 1
  UNION ALL SELECT 3, 'Meena', 2
  UNION ALL SELECT 4, 'Arun', NULL
)
SELECT e.name AS employee, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

Same data. Arun is back. The NULL is not a bug. It is the reason LEFT JOIN exists.

Pick INNER when the report is "employees who belong to a valid department". Pick LEFT when the report is "all employees, and their department if they have one". Most production reports are the second kind. People still type INNER by habit, then hunt for missing rows at 11 pm.

When do you need RIGHT join or FULL join?#

RIGHT JOIN keeps every row from the right table. Empty departments survive. Employees with no department do not, unless they match.

You almost never need to type RIGHT JOIN. Swap the table order and write LEFT JOIN. Same result. Easier to read from left to right. Interviewers still ask it to see if you freeze.

FULL OUTER JOIN keeps unmatched rows from both sides. An employee with no department stays, department columns NULL. A department with no employee stays, employee columns NULL. On the sample data above, Arun would come back with a blank department, and Finance would come back with a blank employee.

PostgreSQL and SQL Server run FULL OUTER JOIN as written. MySQL still does not have it, so you UNION a LEFT JOIN with a RIGHT JOIN and drop the double-counted matches. If the round is on MySQL, say that. It shows you have touched a real engine.

Why would you join a table to itself?#

The manager is also an employee. Same table. Two roles. That is the self join use case.

Alias the table twice. One alias is the person. The other is their manager. Join on manager_id. HR systems, org charts, referral trees, who approved this ticket - all of this.

WITH employees AS (
  SELECT 1 AS id, 'Priya' AS name, NULL AS manager_id
  UNION ALL SELECT 2, 'Karthik', 1
  UNION ALL SELECT 3, 'Meena', 1
  UNION ALL SELECT 4, 'Arun', 1
)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Priya has no manager, so her manager column is NULL. Karthik, Meena and Arun all point at Priya. Use LEFT JOIN here. INNER JOIN makes the CEO vanish, and you look sloppy.

What actually slows a join down?#

The join column without an index is the first thing that hurts.

Ten demo rows feel instant. A few crore order rows at an Indian ecommerce shop do not. Index employees.dept_id. Keep departments.id as the primary key. Same data type on both sides. INT joined to VARCHAR kills the index because the engine has to cast.

Do not SELECT *. You drag columns nobody asked for. Do not use DISTINCT as a mop after a bad join. Filter with WHERE. Read EXPLAIN when a query is slow.

Nested loop is fine when one side is tiny. Hash join is what you want when both sides are large. You rarely pick this by hand. You give the planner indexes and clean types, then you stop guessing.

Which mistakes get people into trouble?#

Forgetting ON. Some engines then produce a CROSS JOIN - every employee times every department. Fans spin. SQL is not broken. You missed the ON.

Filtering the right table in WHERE after a LEFT JOIN is the silent killer. A condition like d.name = 'Engineering' in WHERE throws away unmatched left rows. The LEFT JOIN quietly becomes INNER. Put right-table filters in the ON clause if you want to keep the unmatched left rows.

Writing INNER JOIN when the business question is "show everyone, even if they never ordered". That bug ships in dashboards every week.

SELECT * from a join gives you two id columns and two name columns. The app maps the wrong one. Spell it out: e.name AS employee, d.name AS department.

Joining on names instead of ids. "Engineering" typed two ways. Matches vanish. Use the key.

Where do Indian interviews actually test this?#

Joins are still the most common SQL question in Indian interviews. Services companies put INNER vs LEFT on the first screening. Product companies skip the definition and drop a schema on you.

The classic: customers and orders. Find customers who never placed an order. That is LEFT JOIN plus WHERE orders.id IS NULL. Next: print each employee with their manager. Self join. Next: count orders per customer including zeros. LEFT JOIN plus GROUP BY.

If you can draw the two circles and write those three queries, you are past the SQL round in most Chennai and Bengaluru interviews.

What should you do now?#

Do not re-read theory. Open PostgreSQL, MySQL, or SQLite and type.

Practice task: make a customers table with id and name, and an orders table with id, customer_id and amount. Four customers. A few orders. Leave at least one customer with zero orders.

Write three queries. Customers who ordered, with totals - INNER JOIN and SUM. All customers including zeros - LEFT JOIN and COALESCE. Customers who never ordered - LEFT JOIN and WHERE orders.id IS NULL.

If those three run clean, you already know more than half the people walking into that cafeteria interview.