SQL SELECT Command Introduction

From SwinBrain

You retrieve data from a relational database using the SELECT command. Getting the data from the database is an important task and SQL gives you a large amount of flexibility here. The basic syntax for this command is as follows.

SELECT syntax:

SELECT (field1 [, field2] ...)
FROM (table1 [, table2] ...)
WHERE (condition1 [, condition2] ...)
ORDER BY (field1 [ASC|DESC] [, field2 [ASC|DESC]] ...)
GROUP BY (field1 [, field2] ...)
HAVING (condition1 [, condition2] ...)
Introduction: The material in this page aims to introduce important concepts rather than provide a detailed examination of the topic.

Contents

Using WHERE

The WHERE clause is used to set specific criteria for many SQL commands, including select, update and delete. You use the WHERE clause to set a condition that is required for a record to be selected.

You can use the following operators in a WHERE: =, <, >, <=, >=, <>, IN, IS, IS NOT, LIKE Conditions can be connected with logical operators including: NOT, AND and OR

Following are some examples of SELECT statements with WHERE clauses. The comments indicate what the WHERE clause is doing.

-- All rows with student_id greater than 1
SELECT * FROM Students
WHERE student_id > 1;
 
-- All rows with student_id equal to 1
SELECT * FROM Students
WHERE student_id = 1;
 
-- All rows with student_id equal to 1 or 2
SELECT * FROM Students
WHERE student_id IN (1, 2);
 
-- Same as above using a logical operator
SELECT * FROM Students
WHERE student_id = 1 OR student_id = 2;
 
-- Look for work_phone entries that are not unknown
SELECT * FROM Students
WHERE work_phone IS NOT NULL;
 
-- First and Last names must match supplied values
SELECT * FROM Students
WHERE last_name = 'Smith' AND first_name = 'Jo';
 
-- First or Last names must match supplied values
SELECT * FROM Students
WHERE last_name = 'Smith' OR first_name = 'Jo';

Ordering the output of a SELECT command

The ORDER BY clause is used to specify the order of the records (rows) in a query result table. Results can be requested to be ordered in either ascending or descending order using ASC or DESC respectively. If ASC or DESC is not specified, ASC is used by default. If ORDER BY is not specified, the result table defaults to the order that records were stored in the database tables.

Examples:

-- Select all of the authors, ordered by last_name
SELECT * 
FROM authors 
ORDER BY last_name;
 
-- Select all of the authors, ordered by age, descending
SELECT * 
FROM authors 
ORDER BY age DESC;

Aggregating results in a SELECT

We can ask a relational database to do aggregate functions using SQL commands. Aggregate functions are calculations such as a column average, count, maximum, minimum or summation. By default, the results from an aggregate function are not titled with a field name in the result table. We can use the alias command “AS” to give any field or aggregate result a special field name in the result table.

Aggregate/Alias example:

-- Count the number of authors
SELECT COUNT(*) AS num_authors 
FROM authors
 
-- Get the average mark from the grade table
SELECT AVG(mark) FROM Grade;
 
-- Count the number of records in the Student table
SELECT COUNT(*) AS Num_Students FROM Student;
 
-- The largest mark in the Grade table
SELECT MAX(mark) AS Top_Result FROM Grade;
 
-- The sum of the marks for student_id 1 in Grade table
SELECT SUM(mark) FROM Grade
WHERE student_id = 1;

The database also provides a way of grouping the results in the output of a SELECT. The GROUP_BY clause indicates how to group data in the results. This is useful when combined with the aggregate functions as you can group on one set of fields, and get the sum/min/max/average/etc of other fields. When you have an aggregate function in the output, all fields must be within aggregate functions or part of a grouping.

-- Get the sum of the marks for each student_id
SELECT student_id, SUM(mark)
FROM Grade
GROUP BY student_id; -- must be part of the group by to be a valid query
 
-- Get the average marks for each student_id
SELECT student_id, AVG(mark)
FROM Grade
GROUP BY student_id;

Restricting Aggregates

The where clause can be used to restrict the rows that appear for an aggregate, but it cannot be used to restrict the selection based in aggregated values. The HAVING clause allows conditions which are evaluated after the aggregates are calculated.

-- Get the average marks for each student_id,
-- Then restrict to select only those with an
-- average over 65
SELECT student_id, AVG(mark) AS AverageMark
FROM Grade
GROUP BY student_id;
HAVING AverageMark > 65
 
-- Get the average mark for all students in 'HIT1301'
-- 'HIT2302', and 'HIT3197' then
-- restrict to those over 65
SELECT student_id, AVG(mark) AS AverageMark
FROM Grade
WHERE subject_code IN ('HIT1301', 'HIT2302', 'HIT3197')
GROUP BY student_id
HAVING AverageMArk > 65

There are also a TOP, LIMIT and/or OFFSET clauses implemented by some databases (SQL Server, MySQL and Oracle) that allow the limiting of results returned to the client. This is especially useful for the "paging" of SELECT results into smaller result sections, as used in many web-based interfaces like search engine result views and web based email clients that list a limited view of emails. Limiting results sent to the client can also be of major benefit to performance.

Selecting from multiple tables

Using SELECT you can retrieve data from more than one table at a time. The SELECT syntax shown below indicates that the FROM clause takes one or many tables from which to retrieve the data. When this action is performed the database will combines the records from each table with all of the records from the other table. This result can then be restricted using a WHERE clause.

SELECT (field1 [, field2] ...)
FROM (table1 [, table2] ...)
WHERE (condition1 [, condition2] ...)
ORDER BY (field1 [ASC|DESC] [, field2 [ASC|DESC]] ...)
GROUP BY (field1 [, field2] ...)
HAVING (condition1 [, condition2] ...)

Example of multi-table SELECT

In this example we will start with the following tables.

Supplier Table
Supplier Code Supplier
Fred1 Freds Supplies
Stuff We Supply Stuff
... ...
Product Table
Product Code Name Cost Supplier Code
W123-a Widget type 3 $0.25 Fred1
C156-e Large cog $5.40 Stuff
W871 Small Widget type 7 $0.05 Stuff
... ... ... ...

Lets start with the simplest multi-table select.

SELECT *
FROM product, supplier

This SELECT command will select all fields from the combination of product and supplier. The database will then combine all of the product records with all of the supplier records. There are three suppliers and four products, so there will be 12 (3 x 4) records in the combination of the two.

Supplier Table + Product Table
Supplier.Supplier Code Supplier.Supplier Product.Product Code Product.Name Product.Cost Product.Supplier Code
Fred1 Freds Supplies W123-a Widget type 3 $0.25 Fred1
Fred1 Freds Supplies C156-e Large cog $5.40 Stuff
Fred1 Freds Supplies W871 Small Widget type 7 $0.05 Stuff
Fred1 Freds Supplies ... ... ... ...
Stuff We Supply Stuff W123-a Widget type 3 $0.25 Fred1
Stuff We Supply Stuff C156-e Large cog $5.40 Stuff
Stuff We Supply Stuff W871 Small Widget type 7 $0.05 Stuff
Stuff We Supply Stuff ... ... ... ...
... ... W123-a Widget type 3 $0.25 Fred1
... ... C156-e Large cog $5.40 Stuff
... ... W871 Small Widget type 7 $0.05 Stuff
... ... ... ... ... ...

Firstly notice that I have added the table name before each column. This is needed to uniquely identify commonly named fields, for example Supplier Code exists in both the supplier and the product table. At the moment the query is not using the relationship that exists between the tables that links each product to its supplier. Notice that all suppliers are linked to all products and visa versa. Also notice that the Product.Supplier Code field contains the value of the Supplier Code that is linked to by this product. This information can be used in the WHERE clause as shown below.

SELECT *
FROM product, supplier
WHERE product.[Supplier Code] = supplier.[Supplier Code]

This query instructs the database to only select rows where the two Supplier Code fields match. The following table shows the results of this. The crossed out values have now been excluded.

Supplier Table + Product Table (restricted)
Supplier.Supplier Code Supplier.Supplier Product.Product Code Product.Name Product.Cost Product.Supplier Code
Fred1 Freds Supplies W123-a Widget type 3 $0.25 Fred1
Fred1 Freds Supplies C156-e Large cog $5.40 Stuff
Fred1 Freds Supplies W871 Small Widget type 7 $0.05 Stuff
Fred1 Freds Supplies ... ... ... ...
Stuff We Supply Stuff W123-a Widget type 3 $0.25 Fred1
Stuff We Supply Stuff C156-e Large cog $5.40 Stuff
Stuff We Supply Stuff W871 Small Widget type 7 $0.05 Stuff
Stuff We Supply Stuff ... ... ... ...
... ... W123-a Widget type 3 $0.25 Fred1
... ... C156-e Large cog $5.40 Stuff
... ... W871 Small Widget type 7 $0.05 Stuff
... ... ... ... ... ...

Now that we have selected the records with matching Supplier Codes we can select the fields that we want to appear.

SELECT supplier.[Supplier Code], Supplier, [Product Code], Cost
FROM product, supplier
WHERE product.[Supplier Code] = supplier.[Supplier Code];

The results of this query are shown in the following table. Here you can see that we have each product with its associated supplier's details.

Partial Supplier Table + Product Table (restricted)
Supplier.Supplier Code Supplier.Supplier Product.Product Code Product.Name Product.Cost
Fred1 Freds Supplies W123-a Widget type 3 $0.25
Stuff We Supply Stuff C156-e Large cog $5.40
Stuff We Supply Stuff W871 Small Widget type 7 $0.05
... ... ... ... ...

Links

Links on SwinBrain related to SQL SELECT are:

Also check out the following external links.

[edit]Links: Topic Brains
Return to Swin Brain