Only the columns you need

Problem

The newsletter list

The shop sends a newsletter with offers. Your colleague in marketing needs only the customers' names and emails.

SELECT * gives everything: id, city, sign-up date. With 20 customers they will delete the rest by hand. With 200,000 and ten columns, they will not.

The idea

List the columns

Instead of *, write the columns you want, separated by commas:

SQLdatabase: shop

SELECT name, email FROM customers;

SELECT name, email FROM customers;

▶ RunRestore the queryFresh database

The columns come in the order you wrote them. Swap them and run again.

This holds for every query from now on: take only what you need. The database sends less data, and whoever reads the query sees exactly what is used.

Try it

The price list

Show only the name and price of each product.

SQLdatabase: shop

SELECT * FROM products;

SELECT * FROM products;

Check▶ Run

See it

One comma

This query looks almost like the first one. Run it:

SQLdatabase: shop

SELECT name email FROM customers;

SELECT name email FROM customers;

▶ RunRestore the queryFresh database

1. Why did SELECT name email FROM customers; return a single column called email?

SQLite cannot show two columnsThe email column is emptyWithout a comma, email became a new name for the name column

Check

The idea

Other names with AS

A column can be shown under another name. That is called an alias and is written with AS:

SQLdatabase: shop

SELECT name AS customer, email AS "newsletter email"
FROM customers;

SELECT name AS customer, email AS "newsletter email" FROM customers;

▶ RunRestore the queryFresh database

A name without spaces is written as it is. If it has spaces, put it in double quotes. Single quotes in SQL are for text; you will use them in the next lesson.

An alias does not change the table. It only changes the column heading in this result.

Try it

The stock

Show the name and stock of the products, but call the columns product and in_stock.

SQLdatabase: shop

SELECT name, stock FROM products;

SELECT name, stock FROM products;

Check▶ Run

Exercise

The orders in brief

For each order show its number, date and status, without the customer.

SQLdatabase: shop

Check▶ Run

Test

Check yourself

1. What does SELECT email, name FROM customers; return?

Only the emailsBoth columns, email firstAn error: columns must be in table order

2. When does an alias go in double quotes?

AlwaysNever, quotes are for textWhen it has spaces, non-Latin letters or is a keyword

3. Why is SELECT name, price better than SELECT * in a program?

There is no differenceBecause * is slow to typeBecause you take only what you need and the code does not break when a column is added

Check

Start the lesson over