Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Understanding Indexes

Introduction
Single-column indexes
Multi-column indexes
Additional options when creating indexes
9. NULLS FIRST and NULLS LAST
Summary

Instruction

You can also add one more bit of information to your index definition. We're given the same table:

CREATE TABLE player (
  id integer PRIMARY KEY,
  first_name varchar(64),
  last_name varchar(64),
  year_born integer,
  country varchar(64),
  current_points integer
);

By default, NULL values appear last when sorting rows in ascending order; they appear first when sorting rows in descending order. When we create an index for country and current_points, we can specify where NULL values should appear by adding NULLS FIRST or NULLS LAST:

CREATE INDEX player_multi_country_points_index
ON player(country, current_points DESC NULLS LAST);

In the index above, NULL values in current_points will appear last, even though the index uses descending order.

Exercise

Modify this index:

CREATE INDEX city_rating_index
ON driver(city ASC, rating DESC);

This time, specify that NULL values for the column city should appear first, while NULL values for the column rating should be shown last.

Stuck? Here's a hint!

Use NULLS FIRST and NULLS LAST.