SQL SELECT Statement

What does the SELECT statement do?

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT column1, column2, .... // attribute list
FROM table_name;

If you want to select all the fields available in the table, use the following syntax with the ( * ):

SELECT * FROM table_name;

Duplicate Tuples

SQL does not automatically eliminate duplicate tuples in query results. Solution: #SELECT DISTINCT Alternative

SELECT DISTINCT Alternative

A column can often contains duplicate values.

The SELECT DISTINCT statement can be used to return only distinct (different) values.

To only list the different (distinct) values, we use the following syntax:

SELECT DISTINCT column1, column2, ...
FROM tablename;

Retrieving information from different tables

You can use the select statement to retrieve data from multiple different tables:

SELECT column1, column2,...
FROM table1, table2,...
WHERE <some condition based on related data between the 2 tables to link>

Connection Required

When retrieving information from multiple different tables, a connection between the two tables is required because the tables might not be related in any way, and there needs to be a way to connect the data in the two tables in a meaningful way. The best way to address this is using the JOIN statement. However, this can also be done using the WHERE clause to link the 2 tables. See example below.

SELECT orders.order_id, customers.customer_name, orders.order_date
FROM orders, customers
WHERE orders.customer_id = customers.customer_id // matching id in both tables so that only the rows returned are those where the id matches in both
AND customers.city = 'New York';