Projection Queries ​

Using SELECT * FROM <table_name> WHERE <condition> allows us to select multiple records from a table, where the returned two-dimensional table structure matches the original table, meaning all columns in the result set correspond to all columns in the original table.

If we only want to return specific columns instead of all columns, we can use SELECT column1, column2, column3 FROM ..., which restricts the result set to include only the specified columns. This operation is known as a projection query.

For example, to return the id, score, and name columns from the students table:

-- Using a projection query
SELECT id, score, name FROM students;

The result set will only include the specified columns, and the order of the columns in the result set can differ from the original table.

When using SELECT column1, column2, column3 FROM ..., you can also assign aliases to each column, allowing the result set's column names to differ from those in the original table. The syntax is SELECT column1 alias1, column2 alias2, column3 alias3 FROM ....

For example, the following SELECT statement renames the score column to points, while keeping the id and name column names unchanged:

-- Using a projection query with column renaming:
SELECT id, score points, name FROM students;

Projection queries can also include WHERE conditions to perform more complex queries:

-- Using a projection query with a WHERE condition:
SELECT id, score points, name FROM students WHERE gender = 'M';

Summary ​

Using SELECT * retrieves all columns from a table, while SELECT column1, column2, column3 allows for the return of only specified columns, known as projection. Additionally, the SELECT statement can rename columns in the result set.