SQL Select — Just the Facts

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 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 (COUNT, SUM, MIN, MAX, and AVG) or remove duplicates from the results
• HAVING — filters rows resulting from the GROUP BY clause
• ORDER BY — sorts the results in ascending or descending order by specified column(s). Without an ORDER 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:

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 ;

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.

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 ;

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.

Start Learning

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');