Today, we will cover
IS NULL. They’re pretty simple and very commonly used, so let’s get started.
📌AS: used to rename columns
📌DISTINCT: used to see unique values
📌IS NULL: used to see null values
# Table of Contents
Query 1. Using AS in column name
SELECT film_id, title AS China_Movie, description, rental_rate FROM film WHERE description LIKE ‘%china%’ ORDER BY rental_rate ASC;
Query 2. Using DISTINCT
SELECT DISTINCT rating FROM film;
Query 3. USING NULL
SELECT * FROM address WHERE address2 IS NULL;
Q1. Finding films based in China and changing the column name to ‘China_Movie’, then sorting them by rental rate in ascending order
SELECT (original column name) AS (new column name)
The first query that we are going to use is
AS. This is used with
SELECT to rename columns. We want to give columns distinct names since we export data by writing specific conditions. All we need to do is write the original column name after select and add the new column name after
In this example, we will use
LIKE to search for a pattern that matches information about films based on China. Before that, let’s specify the information we want to see. We want the film ID, title, description, price (rental rate) and we want to search in the ‘film’ table. Oh, let’s also order it by price in ascending order.
SELECT film_id, title, description, rental_rate FROM film WHERE description LIKE’%china%’ ORDER BY rental_rate ASC;
Now we want to rename the column ‘title’ to ‘China Movie’. Let’s do this by using our new query
AS. We’re only renaming one thing here, so we only need to use
AS once in the area we want changed. Plug in
AS after title in the
SELECT query, like this:
SELECT film_id, title AS China_Movie, description, rental_rate ….
The rest of your query will remain the same, the only thing you’ve changed is the section where you rename that one column.
🔑 You can go one step further and rename all the columns!
Here is our example:
SELECT film_id, title AS China_Movie, description AS Summary, rental_rate AS Price FROM film WHERE description LIKE '%china%' ORDER BY rental_rate ASC;
Q2. What are the distinct (different) values in the rating column?
SELECT DISTINCT column
If you want to see film ratings like PG, PG-13, NC-17 in the film table, how can you write your query? Try this:
SELECT film_id, title, description, rating FROM film;
Notice how there are many different movies with the same ratings.
What if we just want to see how many ratings are available, without the film information? We can write it like this:
SELECT rating FROM film;
While this gives us the information we want, it shows multiple results of the same rating. It’ll be easier to sort through this table if we have a list of ratings without duplicates. This is where
DISTINCT comes in. This query works when partnered with
SELECT, so let’s put it into practice to see exactly what ratings are available:
SELECT DISTINCT rating FROM film ORDER by rating;
🔑 We added
ORDER BY here just to get a clearer view of the information.
# IS NULL
Q3. Finding the null value of address2 in the address table
WHERE column IS NULL
Finally, let’s learn how to find the null value in SQL.
📌 The term NULL refers to an empty value. This means there is no information at all.
Let’s check out the address table for a second. Run this query:
SELECT * FROM address;
In the address2 column, you’ll see a few results with “(null)” and the rest are just blank. Even though in our eyes the blanks are also ‘null’, SQL is reading them as having value. The value is just a space! So let’s check out only the columns with blanks by putting a space between our single quotations.
SELECT * FROM address WHERE address2=‘ ’;
Next let’s check out the results where the information is (null). Can we just type
Give it a try!
⚠️ As you can see, we won’t get any results. That’s because we need to use a different query here. Try this one:
SELECT * FROM address WHERE address2 IS NULL;
There we go! Here are all the addresses where the address2 information is null, meaning absolutely nothing was entered (not even a space!) So if you ever want to find (null) columns, you need to use the
IS NULL query.
# Practice Time
📰 Your challenge this time is to find the unique value of rental duration in the film table, and sort it in ascending order. 👍 Take a moment to figure it out on your own before you check out our tutorial below.
Did you get it? Compare your query to the one we’ve provided below!
SELECT DISTINCT rental_duration FROM film ORDER BY rental_duration ASC;
In this example, we have to use
DISTINCT because we want to see the unique values without any duplicates. Without
DISTINCT, we will get rows and rows of information that we don’t need.