Insights into Company
← SQL learning path

NULL Values and Joins

Learn SQL from fundamentals to advanced interview and production-level database concepts through practical examples, questions and quizzes.

Complete SQL Learning & Interview Path

Learn SQL by working with realistic databases instead of memorising isolated commands. Every concept can be connected with practical tables such as students, employees, departments, countries, states, customers, products and orders.

SQL Fundamentals SELECT WHERE JOINs GROUP BY Subqueries Indexes Transactions Window Functions Interview Questions
Start Here

SQL through a real database

SQL is used to store, retrieve, modify and analyse structured data. Instead of learning commands independently, imagine that we are building a small company database.

Student table

A student database can contain identity, name, course and marks.

CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(100), course VARCHAR(100), marks INT );

Employee table

Employee information can be connected with departments, salaries and managers.

CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(100), department_id INT, salary DECIMAL(12,2) );

Country / State table

Geographic data is useful for understanding relationships and JOINs.

CREATE TABLE states ( id INT PRIMARY KEY, name VARCHAR(100), country_id INT );

Customer / Orders

E-commerce data provides practical examples for aggregation, JOINs and transactions.

CREATE TABLE orders ( id INT PRIMARY KEY, customer_id INT, total DECIMAL(12,2), order_date DATE );
id name course marks
1 Rahul Java 85
2 Priya SQL 92
3 Amit React 76
SQL Learning Block 1

Questions 1 – 10

1
Question 24 of 10

How do you find employees without a manager?

Solution

SELECT * FROM employees WHERE manager_id IS NULL;
Use IS NULL, not = NULL.
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 25 of 10

How do you replace NULL salary with 0?

Solution

SELECT name, COALESCE(salary, 0) AS salary FROM employees;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 26 of 10

Difference between NULL and 0?

Solution

  • NULL is unknown or missing; 0 is an actual numeric value.
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 27 of 10

What is an INNER JOIN?

Solution

Returns matches from both tables.
SELECT e.name,d.department_name FROM employees e INNER JOIN departments d ON e.department_id=d.id;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 28 of 10

What is LEFT JOIN?

Solution

Returns all left rows and right-side matches.
SELECT e.name,d.department_name FROM employees e LEFT JOIN departments d ON e.department_id=d.id;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 29 of 10

Find employees who don't belong to any department.

Solution

SELECT e.* FROM employees e LEFT JOIN departments d ON e.department_id=d.id WHERE d.id IS NULL;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 30 of 10

What is RIGHT JOIN?

Solution

Returns all right rows and left-side matches.
SELECT e.name,d.department_name FROM employees e RIGHT JOIN departments d ON e.department_id=d.id;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 31 of 10

What is FULL OUTER JOIN?

Solution

  • Returns matches plus unmatched rows from both sides.
  • MySQL simulates it with UNION.
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 32 of 10

What is a SELF JOIN?

Solution

Joins a table to itself.
SELECT e.name employee,m.name manager FROM employees e LEFT JOIN employees m ON e.manager_id=m.id;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Question 33 of 10

What is CROSS JOIN?

Solution

Produces a Cartesian product.
SELECT * FROM employees CROSS JOIN departments;
Think before looking at the solution
  • What would the SQL query do?
  • Which rows will match?
  • Which columns will appear?
  • What happens when NULL values are involved?
  • Can the query be improved using an index?
Knowledge Check

Quiz after Questions 1 – 10

Test yourself before moving to the next SQL learning block.

1. How do you find employees without a manager?
2. How do you replace NULL salary with 0?
3. Difference between NULL and 0?
4. What is an INNER JOIN?
5. What is LEFT JOIN?
6. Find employees who don't belong to any department.
7. What is RIGHT JOIN?
8. What is FULL OUTER JOIN?
9. What is a SELF JOIN?
10. What is CROSS JOIN?