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
FROM— the table(s) from which data is to be retrieved
JOIN— combine tables by a common column
WHERE— restricts the rows of data returned with a boolean, textual, or arithmetic comparison
GROUP BY— group rows having common values via SQL aggregation functions (
AVG) or remove duplicates from the results
HAVING— filters rows resulting from the
ORDER BY— sorts the results in ascending or descending order by specified column(s). Without an
ORDER BYclause, 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 ;
WHERE clause includes a
LIKE wildcard match to find the country names.
The results set is:
|Iran||فالوده — fālūde|
|Iran||پالوده — pālūde|
|Israel||ארטיק אבטיח — artik avatiach|
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 ;
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
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 ;
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
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 ;
AS clause renames the output columns to make reading the results table more intuitive, less tech-y.
|country||desserts per country|
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 ( number DECIMAL, taste VARCHAR(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 ;
|India||falooda||rose syrup, vermicelli, sweet basil|
Our traveler is all set to go, with a checklist of tasty treats to ensure nothing is missed.
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.
SQL Sample Table Code
CREATE TABLE frozen ( id DECIMAL NOT NULL UNIQUE, name VARCHAR(33), origin VARCHAR(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 ( number DECIMAL, taste VARCHAR(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');