ORDER BY with NULL values behavior in SQL - Mini Project: Build & Apply

ORDER BY with NULL values behavior

📖 Scenario: You work in a company database where some employees have not yet been assigned a department. You want to list all employees sorted by their department, but you want to control where the employees with no department (NULL) appear in the list.

🎯 Goal: Build a SQL query that orders employees by their department name, showing employees with NULL department either first or last using ORDER BY with NULLS FIRST and NULLS LAST.

📋 What You'll Learn

Create a table called employees with columns id (integer), name (text), and department (text, nullable).

Insert exactly these rows into employees: (1, 'Alice', 'Sales'), (2, 'Bob', NULL), (3, 'Charlie', 'HR'), (4, 'Diana', NULL), (5, 'Eve', 'IT').

Write a query to select all columns from employees ordered by department with NULL values appearing first.

Write a query to select all columns from employees ordered by department with NULL values appearing last.

💡 Why This Matters

🌍 Real World

Handling NULL values in sorting is common when working with real-world data where some information may be missing or optional.

💼 Career

Knowing how to control the position of NULLs in ordered query results is important for data analysis, reporting, and database management roles.

1

Create the employees table and insert data

Create a table called employees with columns id as integer, name as text, and department as text that can be NULL. Then insert these exact rows: (1, 'Alice', 'Sales'), (2, 'Bob', NULL), (3, 'Charlie', 'HR'), (4, 'Diana', NULL), (5, 'Eve', 'IT').

SQL

-- Create the employees table and insert the rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO with multiple rows to add data.

2

Write a query to order employees by department with NULLs first

Write a SQL query to select all columns from employees and order the results by department so that rows with NULL department appear first.

SQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    department TEXT
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', NULL),
(3, 'Charlie', 'HR'),
(4, 'Diana', NULL),
(5, 'Eve', 'IT');

-- Write a SELECT query ordering by department with NULLS FIRST
-- Your code here

Hint

Use ORDER BY department NULLS FIRST to put NULL values at the top.

3

Write a query to order employees by department with NULLs last

Write a SQL query to select all columns from employees and order the results by department so that rows with NULL department appear last.

SQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    department TEXT
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', NULL),
(3, 'Charlie', 'HR'),
(4, 'Diana', NULL),
(5, 'Eve', 'IT');

SELECT * FROM employees
ORDER BY department NULLS FIRST;

-- Write a SELECT query ordering by department with NULLS LAST
-- Your code here

Hint

Use ORDER BY department NULLS LAST to put NULL values at the bottom.

4

Complete the SQL script with both queries

Ensure your SQL script includes the CREATE TABLE statement, the INSERT INTO statement with the exact rows, and both SELECT queries: one ordering by department with NULLs first, and the other ordering by department with NULLs last.

SQL

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    department TEXT
);

INSERT INTO employees (id, name, department) VALUES
(1, 'Alice', 'Sales'),
(2, 'Bob', NULL),
(3, 'Charlie', 'HR'),
(4, 'Diana', NULL),
(5, 'Eve', 'IT');

SELECT * FROM employees
ORDER BY department NULLS FIRST;

SELECT * FROM employees
ORDER BY department NULLS LAST;

-- Finalize your SQL script here
-- Your code here

Hint

Make sure your script has all parts: table creation, data insertion, and both queries.

Practice

1. What does the ORDER BY clause do when sorting a column that contains NULL values by default in SQL?

easy

A. It treats NULL as zero and sorts accordingly.

B. It places NULL values at the beginning when sorting in ascending order.

C. It places NULL values at the end when sorting in ascending order.

D. It causes a syntax error if NULL values exist.

Solution

1. Step 1: Understand default ORDER BY behavior

   By default, when sorting ascending, SQL places NULL values last because NULL means unknown or missing data.

2. Step 2: Confirm NULL placement in ascending order

   Since NULL is considered neither high nor low, it appears after all actual values in ascending order.

3. Final Answer:

   It places NULL values at the end when sorting in ascending order. -> Option C

4. Quick Check:

   Default ascending sort puts NULL last [OK]

Hint: Remember: NULLs come last by default in ascending order [OK]

Common Mistakes:

• Thinking NULLs are treated as zero
• Assuming NULLs appear first by default
• Believing NULLs cause errors in ORDER BY

2. Which of the following is the correct syntax to sort a column named score in ascending order but place NULL values first?

easy

A. ORDER BY score ASC NULLS FIRST

B. ORDER BY score ASC NULLS LAST

C. ORDER BY score NULLS FIRST ASC

D. ORDER BY NULLS FIRST score ASC

Solution

1. Step 1: Recall correct syntax for NULL placement

   The correct syntax places NULLS FIRST or NULLS LAST after the sort direction (ASC or DESC).

2. Step 2: Match syntax with options

   ORDER BY score ASC NULLS FIRST uses ORDER BY score ASC NULLS FIRST, which is the correct order of keywords.

3. Final Answer:

   ORDER BY score ASC NULLS FIRST -> Option A

4. Quick Check:

   NULLS FIRST comes after ASC/DESC [OK]

Hint: Put NULLS FIRST or LAST after ASC/DESC in ORDER BY [OK]

Common Mistakes:

• Placing NULLS FIRST before column name
• Mixing order of ASC/DESC and NULLS FIRST
• Using NULLS FIRST without ASC or DESC

3. Given the table students with column grade containing values (85, NULL, 90, 70), what is the result of this query?

SELECT grade FROM students ORDER BY grade DESC NULLS LAST;

medium

A. [NULL, 90, 85, 70]

B. [90, 85, 70, NULL]

C. [90, 85, 70]

D. [70, 85, 90, NULL]

Solution

1. Step 1: Sort grades descending with NULLs last

   Descending order sorts from highest to lowest: 90, 85, 70. NULLs go last as specified.

2. Step 2: Arrange final output

   So the order is 90, 85, 70, then NULL at the end.

3. Final Answer:

   [90, 85, 70, NULL] -> Option B

4. Quick Check:

   DESC with NULLS LAST puts NULL at end [OK]

Hint: DESC sorts high to low; NULLS LAST puts NULL at end [OK]

Common Mistakes:

• Placing NULL at start despite NULLS LAST
• Ignoring NULLS LAST and using default NULL position
• Sorting ascending instead of descending

4. Identify the error in this SQL query:

SELECT name FROM employees ORDER BY salary NULLS FIRST ASC;

medium

A. The order of NULLS FIRST and ASC is incorrect.

B. NULLS FIRST cannot be used with ASC.

C. Missing comma between salary and NULLS FIRST.

D. The query is correct and will run without errors.

Solution

1. Step 1: Check correct syntax order for ORDER BY

   The correct syntax is ORDER BY salary ASC NULLS FIRST. NULLS FIRST must come after ASC or DESC.

2. Step 2: Identify error in given query

   The query places NULLS FIRST before ASC, which is invalid syntax.

3. Final Answer:

   The order of NULLS FIRST and ASC is incorrect. -> Option A

4. Quick Check:

   NULLS FIRST must follow ASC/DESC [OK]

Hint: NULLS FIRST/LAST always after ASC or DESC [OK]

Common Mistakes:

• Placing NULLS FIRST before ASC/DESC
• Adding commas in ORDER BY clause
• Assuming NULLS FIRST is invalid with ASC

5. You want to list products by price ascending but ensure that products with unknown price (NULL) appear at the top. Which query achieves this correctly?

hard

A. SELECT * FROM products ORDER BY price ASC NULLS LAST;

B. SELECT * FROM products ORDER BY price NULLS FIRST ASC;

C. SELECT * FROM products ORDER BY NULLS FIRST price ASC;

D. SELECT * FROM products ORDER BY price ASC NULLS FIRST;

Solution

1. Step 1: Understand requirement for NULL placement

   We want ascending order but NULL values first, so use NULLS FIRST after ASC.

2. Step 2: Check syntax correctness

   SELECT * FROM products ORDER BY price ASC NULLS FIRST; uses ORDER BY price ASC NULLS FIRST, which is valid and meets the requirement.

3. Final Answer:

   SELECT * FROM products ORDER BY price ASC NULLS FIRST; -> Option D

4. Quick Check:

   ASC with NULLS FIRST puts NULLs on top [OK]

Hint: Use ASC NULLS FIRST to put NULLs at top in ascending sort [OK]

Common Mistakes:

• Placing NULLS FIRST before ASC
• Using NULLS LAST instead of NULLS FIRST
• Misordering keywords causing syntax errors