Relational databases store information in tables — in columns that describe aspects of an item and rows which tie together the columns. The SQL Select operation, often called “the query,” retrieves data from one (or more) tables or expressions (like counting entries in a table).
Arguments to the SQL Select describe the desired data, which the underlying database uses to plan, optimise, and perform retrieval. This blog entry demonstrates the SQL Select operation and the argument types permitted.
The syntax of a typical SQL Select statement is:
SELECT
column1, column2, ...
FROM
table1, table2, …
WHERE
Condition
ORDER BY
columnX ASC|DESC
Instead of one or more columns, an asterisk — *
— may denote the query should return all the columns of the specified tables. The keywords which may follow the SELECT
include:
FROM
— the table(s) from which data is to be retrievedJOIN
— combine tables by a common columnWHERE
— restricts the rows of data returned with a boolean, textual, or arithmetic comparisonGROUP BY
— group rows having common values via SQL aggregation functions (COUNT
,SUM
,MIN
,MAX
, andAVG
) or remove duplicates from the resultsHAVING
— filters rows resulting from theGROUP BY
clauseORDER BY
— sorts the results in ascending or descending order by specified column(s). Without anORDER BY
clause, the order of rows returned by a query is undefined.DISTINCT
— eliminates duplicates from the results
The remainder of this blog entry will develop a scenario and provide the SQL Select statements necessary to satisfy each evolving goal. Many of the clauses listed above and demonstrated below show further treatment; check our hub of SQL tutorials.
Scenario — Planning For a Tasty Vacation
A tech-savvy traveler with a sweet-tooth wants to ensure that they don’t miss frozen confectionaries on future world trips. Creating a table — here called frozen — that contains such treats and their country of origin is the first step to tasting them all. (The SQL to create and populate the table is given at the bottom of this blog entry.)
Wildcard Searches Help Narrow Options
Thus far, the plan is to visit countries that begin with the letter “I” — the SQL to retrieve our options is:
SELECT
origin, name
FROM
frozen
WHERE
origin LIKE 'I%'
ORDER BY
name ASC ;
The WHERE
clause includes a LIKE
wildcard match to find the country names.
The results set is:
origin | name |
Iceland | nýi ís |
India | falooda |
India | kulfi |
Indonesia | es doger |
Iran | فالوده — fālūde |
Iran | پالوده — pālūde |
Israel | ארטיק אבטיח — artik avatiach |
Italy | gelato |
Italy | sorbetto |
Some things of note in this results set include:
- The column name(s) appear in the top row
- Internationalization supports right-to-left languages and accent characters
IN, =, And AND Extract Specific Choices
The decision has been made to visit Italy and India, so one way to fashion the SQL Select becomes:
SELECT
name, origin
FROM
frozen
WHERE
origin = 'India' OR origin = 'Italy'
ORDER BY
name ASC ;
The changed WHERE
clause now includes two equality tests (does this value match that text string) and a boolean operator, OR
, to link together the two tests, returning true if either of their comparisons is true. (For those thinking of crafting something similar for a specific problem, the “not equals” test, !=
, and the boolean operators AND
and NOT
are handy.)
Experienced programmers will recoil at the repeated “origin =
” phrases, realizing these become fertile places for human error, especially as the number of items being compared increases. SQL provides the IN
operator to produce the same results set in a more programmer-friendly way:
SELECT
name, origin
FROM
frozen
WHERE
origin IN ( 'India', 'Italy' )
ORDER BY
name ASC ;
name | origin |
falooda | India |
gelato | Italy |
kulfi | India |
sorbetto | Italy |
Aggregate Functions Provide Results Overviews
There’s some discussion of pruning the trip to just one country, considering the number of desserts available in each country, computed by COUNT(origin)
. We ORDER BY COUNT(origin) ASC
to place the highest count country appears at the top of the table to make reading the results easier for these travel decision-makers.
SELECT
Origin AS 'country',
COUNT(origin) AS 'desserts per country'
FROM
frozen
WHERE
origin IN ( 'India', 'Italy' )
GROUP BY
origin
ORDER BY
COUNT(origin) ASC ;
The AS
clause renames the output columns to make reading the results table more intuitive, less tech-y.
country | desserts per country |
India | 2 |
Italy | 2 |
Unfortunately for our traveler, both countries feature two frozen desserts, so some other criteria will need to break the tie.
Join Tables Together
To evaluate a proposal to visit both countries we make a checklist of all the dessert options. The flavors table contains all available tastes of each dessert, described as:
CREATE TABLE flavors (
numberDECIMAL,
tasteVARCHAR(50)
);
The values in the number column are the same values as used in the frozen table’s id column, so we can perform an INNER JOIN to build out a complete list of desserts and flavors:
SELECT
frozen.origin, frozen.name, flavors.taste
FROM
frozen
INNER JOIN
flavors ON frozen.id = flavors.number
ORDER BY
origin, name, taste ASC ;
origin | name | taste |
India | falooda | rose syrup, vermicelli, sweet basil |
Italy | gelato | pistachio |
Italy | gelato | stracciatella |
Italy | sorbetto | lemon |
Italy | sorbetto | lime |
Italy | sorbetto | orange |
USA | popsicle | lemon |
USA | popsicle | orange |
USA | popsicle | pineapple |
USA | popsicle | strawberry |
Our traveler is all set to go, with a checklist of tasty treats to ensure nothing is missed.
Conclusion
Covered here are the basics of the SQL Select statement, with basic information on the most-used clauses.
Several evolutions of a use case are developed, with a focus on wildcard searches; several ways to filter results by values in a set; renaming result column names; grouping, ordering, and counting results; and joining related tables by a common column.
To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.
SQL Sample Table Code
The following will build and populate the sample data referred to in this blog post. Test SQL interactively at SQL Fiddle or ExtendsClass.
CREATE TABLE frozen (
idDECIMAL NOT NULL UNIQUE,
nameVARCHAR(33),
originVARCHAR(33)
);
INSERT INTO frozen
VALUES
( 1, 'baobing', 'Taiwan' ),
( 2, 'dondurma', 'Turkey' ),
( 3, 'falooda', 'India' ),
( 4, 'frozen yoghurt', 'USA' ),
( 5, 'gelato', 'Italy' ),
( 6, 'jipangi', 'Korea' ),
( 7, 'kulfi', 'India' ),
( 8, 'mochi', 'Japan' ),
( 9, 'popsicle', 'USA' ),
( 10, 'soft serve', 'USA' ),
( 11, 'sorbetto', 'Italy' ),
( 12, 'stir-fried', 'Thailand' ),
( 13, 'nýi ís', 'Iceland' ),
( 14, 'es doger', 'Indonesia' ),
( 15, 'فالوده — fālūde', 'Iran' ),
( 16, 'پالوده — pālūde', 'Iran' ),
( 17, 'ארטיק אבטיח — Artik Avatiach', 'Israel' ) ;
CREATE TABLE flavors (
numberDECIMAL,
tasteVARCHAR(50)
);
INSERT INTO flavors
VALUES
(5, 'pistachio'),
(5, 'stracciatella'),
(11, 'orange'),
(11, 'lime'),
(11, 'lemon'),
(3, 'rose syrup, vermicelli, sweet basil'),
(9, 'pineapple'),
(9, 'lemon'),
(9, 'orange'),
(9, 'strawberry');