Back to Basics: Writing SQL Queries

Almost all applications store data in one format or another somewhere. Us developers spend a lot of time thinking about our data. In a most cases we end up storing that data in a relational database. The advent of the ORM has made writing raw SQL much less common, but when we do it’s good to have an understanding of the fundamental elements of the language. Today we’ll go over some basic queries and the syntax required to make them.

Setup

For the sake of simplicity we’ll use SQLite3 for this blog. It’s important to note that none of the topics or commands we’ll look at are specific to SQLite3. All the query examples we’ll go over below are ISO 9705 compliant and will work in any of the major databases (Postgresql, MySql, Oracle, etc).

From the command line fire up SQLite3 and create a database named back_to_basics:

% sqlite3 back_to_basics

Now let’s create three tables. We’ll call them players, teams and players_teams:

SQLite3 version 3.7.12 2012-04-03 19:43:07
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE players (id INTEGER PRIMARY KEY ASC, name TEXT, seasons_played INTEGER);
sqlite> CREATE TABLE teams (id INTEGER PRIMARY KEY ASC, name TEXT);
sqlite> CREATE TABLE players_teams (player_id INTEGER, team_id INTEGER, won_championship BOOLEAN);
sqlite> .tables
players        players_teams  teams

Before we’re done with the setup stage let’s turn on explain mode. This will let us see our column names in query results:

sqlite> .explain on

Our Data

In order to write any queries we’ll need some data, which means we need some players and teams. We’ll use baseball since players tend to move around more in that sport.

Let’s enter information on three baseball players and the first two teams they played for in their careers (just to keep the data set small).

Player First Team Second Team
Nolan Ryan New York Mets California Angels
Jim Sundberg Texas Rangers Milwaukee Brewers
Ivan Rodriguez Texas Rangers Florida Marlins

INSERT

To get our player data into the database we’ll use the INSERT statement:

sqlite> INSERT INTO players (name, seasons_played) VALUES ('Nolan Ryan', 27);
sqlite> INSERT INTO players (name, seasons_played) VALUES ('Jim Sundberg', 16);
sqlite> INSERT INTO players (name, seasons_played) VALUES ('Ivan Rodriguez', 21);

SELECT

Now that we have data in our first table let’s run a query and make sure it looks right. We’ll use the SELECT statement to do this. For our first query we’ll just ask the database to return all rows and columns from our players table. We’ll use the * operator to do this:

sqlite> SELECT *
   ...> FROM players;
id    name           seasons_played
----  -------------  --------------
1     Nolan Ryan     27
2     Jim Sundberg   16
3     Ivan Rodriguez 21

In place of the * operator we can also give the SELECT statement a list of columns. This will limit the result set to just the columns we’re interested in:

sqlite> SELECT name
   ...> FROM players;
name
----
Nolan Ryan
Jim Sundberg
Ivan Rodriguez

ORDER BY

We can also order our results how we’d like. This is done by using the ORDER BY clause:

sqlite> SELECT *
   ...> FROM players
   ...> ORDER BY seasons_played;
id    name           seasons_played
----  -------------  --------------
2     Jim Sundberg   16
3     Ivan Rodriguez 21
1     Nolan Ryan     27

Previously our results were always ordered by id. Because we used the ORDER BY clause we get results ordered by the seasons_played column.

You can also specify if you would like to order results descending:

sqlite> SELECT *
   ...> FROM players
   ...> ORDER BY seasons_played DESC;
id    name           seasons_played
----  -------------  --------------
1     Nolan Ryan     27
3     Ivan Rodriguez 21
2     Jim Sundberg   16

A Little More Setup

Now that we know how to insert data, and query to ensure we entered it correctly, let’s add data to our teams table:

sqlite> INSERT INTO teams (name) VALUES ('Texas Rangers');
sqlite> INSERT INTO teams (name) VALUES ('Florida Marlins');
sqlite> INSERT INTO teams (name) VALUES ('New York Mets');
sqlite> INSERT INTO teams (name) VALUES ('California Angels');
sqlite> INSERT INTO teams (name) VALUES ('Milwaukee Brewers');
sqlite> INSERT INTO teams (name) VALUES ('New York Yankees');

Junction Table

Now we need to connect our players and teams. We’ll do this in our players_teams table. This is what is called a junction table. Junction tables are used to create many to many relationships in relational databases. They achieve this by combining common data from multiple tables. In our case we’re going to include the id columns from our teams and players tables thus allowing us to relate rows from one to the other.

We’ll start with Nolan. His first two teams were the New York Mets and the California Angels. First we need to get our ids:

sqlite> SELECT *
   ...> FROM players;
id    name           seasons_played
----  -------------  --------------
1     Nolan Ryan     27
2     Jim Sundberg   16
3     Ivan Rodriguez 21

Nolan’s id is 1. Let’s find out what the Mets and Angles ids are:

sqlite> SELECT *
   ...> FROM teams;
id    name
----  -------------
1     Texas Rangers
2     Florida Marlins
3     New York Mets
4     California Angels
5     Milwaukee Brewers
6     New York Yankees

The Mets have an id of 3 and the Angels have an id of 4. Nolan won a World Series with the Mets. We now have enough information to write our INSERT statements:

sqlite> INSERT INTO players_teams (player_id, team_id, won_championship) VALUES (1, 4, 0);
sqlite> INSERT INTO players_teams (player_id, team_id, won_championship) VALUES (1, 3, 1);

In the INSERT statements above we associate the player with id 1 (Nolan) to the teams with id 3 (Mets) and 4 (Angels) and provide a 0, which represents false, for the won_championship column for the Angels and a 1, for true, for the won_championship column for the Mets. As you can see we’re able to create relationships between tables by using the ids our database is generating for each of our rows. This is one of the corner stones of the relational database and is called a primary key.

Now we just need to finish up building our players_teams table.

Jim played for the Rangers and Brewers and didn’t win a championship with either:

sqlite> INSERT INTO players_teams (player_id, team_id, won_championship) VALUES (2, 1, 0);
sqlite> INSERT INTO players_teams (player_id, team_id, won_championship) VALUES (2, 5, 0);

Ivan played for the Rangers and the Marlins and won a championship with the Marlins:

sqlite> INSERT INTO players_teams (player_id, team_id, won_championship) VALUES (3, 1, 0);
sqlite> INSERT INTO players_teams (player_id, team_id, won_championship) VALUES (3, 2, 1);

INNER JOIN

Now that we have some data to play with let’s write some multi-table queries.

In order to this we’ll have to use joins. The most common join we have in our tool box is called an INNER JOIN. An INNER JOIN allows us to combine two tables based on a condition we provide. The only rows from both tables that remain after the join are the rows that satisfy the condition.

Let’s join our players table to our players_teams and take a look at the results on the conditions the players table id matches the players_teams player_id column:

sqlite> SELECT *
   ...> FROM players
   ...> INNER JOIN players_teams ON players.id = players_teams.player_id;
id    name           seasons_played  player_id  team_id  won_championship
----  -------------  --------------  ---------  -------  -------------
1     Nolan Ryan      27             1          4        0
1     Nolan Ryan      27             1          3        1
2     Jim Sundberg    16             2          1        0
2     Jim Sundberg    16             2          5        0
3     Ivan Rodriguez  21             3          1        0
3     Ivan Rodriguez  21             3          2        1

The condition we discussed above is what comes after the ON keyword.

Notice that the rows in the players table have all been doubled. This is because the INNER JOIN preserves the number of rows from both tables, so since there are two rows in the players_teams table for every one row in the players table (because we’re looking at each player’s first two teams) we see the rows from the players table twice.

Earlier when we inserted data into our teams table, we added a row for the New York Yankees, but we actually don’t have a player who spent one of their first two seasons with the Yankees.

Let’s see what happens if we INNER JOIN the teams table and players_teams table:

sqlite> SELECT *
   ...> FROM teams
   ...> INNER JOIN players_teams ON teams.id = players_teams.team_id;
id    name               player_id  team_id  won_championship
----  -------------      ---------  -------  ----------------
4     California Angels  1          4        0
3     New York Mets      1          3        1
1     Texas Rangers      2          1        0
5     Milwaukee Brewers  2          5        0
1     Texas Rangers      3          1        0
2     Florida Marlins    3          2        1

Because we used an INNER JOIN the only rows present are the rows that satisfy our condition and in this case the New York Yankees do not, so that row isn’t present in our result set.

We’re not limited to joining just two tables either. Let’s inner join all three of our tables together and see what that looks like:

sqlite> SELECT *
   ...> FROM players
   ...> INNER JOIN players_teams ON players.id = players_teams.player_id
   ...> INNER JOIN teams ON players_teams.team_id = teams.id;
id    name           seasons_played  player_id  team_id  won_championship  id  name
----  -------------  --------------  ---------  -------  ----------------  --  -------------
1     Nolan Ryan      27             1          4        0                 4   California Angels
1     Nolan Ryan      27             1          3        1                 3   New York Mets
2     Jim Sundberg    16             2          1        0                 1   Texas Rangers
2     Jim Sundberg    16             2          5        0                 5   Milwaukee Brewers
3     Ivan Rodriguez  21             3          1        0                 1   Texas Rangers
3     Ivan Rodriguez  21             3          2        1                 2   Florida Marlins

We can also take advantage of the SELECT statement we learned about above to create more readable results sets. Let’s look at a list of players and the teams they played on only:

sqlite> SELECT players.name, teams.name
   ...> FROM players
   ...> INNER JOIN players_teams ON players.id = players_teams.player_id
   ...> INNER JOIN teams ON players_teams.team_id = teams.id;
name            name
----            -------------
Nolan Ryan      California Angels
Nolan Ryan      New York Mets
Jim Sundberg    Texas Rangers
Jim Sundberg    Milwaukee Brewers
Ivan Rodriguez  Texas Rangers
Ivan Rodriguez  Florida Marlins

LEFT OUTER JOIN

A LEFT OUTER JOIN is very similar to an INNER JOIN with one big exception. If a row in the table being joined on does not match the condition specified in the join, the row still remains in the result set.

Let’s look at the query from above where we joined players_teams to teams again. As we recall, when we used an INNER JOIN on the two tables the Yankees were omitted form the result set. Let’s see what happens when we LEFT OUTER join the two tables:

sqlite> SELECT *
   ...> FROM teams
   ...> LEFT OUTER JOIN players_teams ON teams.id = players_teams.team_id;
id    name               player_id  team_id  won_championship
----  -------------      ---------  -------  ----------------
1     Texas Rangers      2          1        0
1     Texas Rangers      3          1        0
2     Florida Marlins    3          2        1
3     New York Mets      1          3        1
4     California Angels  1          4        0
5     Milwaukee Brewers  2          5        0
6     New York Yankees

The Yankees show up in our result set, but with no values in the columns associated with the players_teams table.

Like INNER JOIN we can also specify columns in the SELECT, we can LEFT OUTER JOIN multiple tables and we can also mix INNER JOIN and LEFT OUTER JOIN in the same query.

WHERE

The WHERE clause gives us the ability to specify a condition that will be applied to every row in our final result set. If the condition is not met the row will not remain part of the result set.

Let’s take a look at a list of all the players in our database that spent over 20 years in the league:

sqlite> SELECT *
   ...> FROM players
   ...> WHERE seasons_played > 20;
id    name           seasons_played
----  -------------  --------------
1     Nolan Ryan     27
3     Ivan Rodriguez 21

Our result set only includes the two players with over 20 years.

There are lots of different operators we can use in our WHERE clauses. Above we used the greater than operator. Let’s take a look at a few more of our options.

We can look for rows that meet equality conditions:

sqlite> SELECT *
   ...> FROM players
   ...> WHERE seasons_played = 16;
id    name           seasons_played
----  -------------  --------------
2     Jim Sundberg   16

We can look for rows that contain values between two values:

sqlite> SELECT *
   ...> FROM players
   ...> WHERE seasons_played BETWEEN 20 and 22;
id    name           seasons_played
----  -------------  --------------
3     Ivan Rodriguez 21

We can look for rows that contain a value in a set we provide:

sqlite> SELECT *
   ...> FROM players
   ...> WHERE seasons_played IN (16, 27);
id    name           seasons_played
----  -------------  --------------
1     Nolan Ryan     27
2     Jim Sundberg   16

We can also use the WHERE clause on result sets that have been created by joining multiple tables. Let’s look at a list of players that have won a championship:

sqlite> SELECT *
   ...> FROM players
   ...> INNER JOIN players_teams ON players.id = players_teams.player_id
   ...> WHERE won_championship = 1;
id    name           seasons_played  player_id  team_id  won_championship
----  -------------  --------------  ---------  -------  ----------------
1     Nolan Ryan      27             1          3        1
3     Ivan Rodriguez  21             3          2        1

We can also use the WHERE clause on two columns from two different tables by concatenating with an AND or OR:

sqlite> SELECT *
   ...> FROM players
   ...> INNER JOIN players_teams ON players.id = players_teams.player_id
   ...> WHERE players_teams.won_championship = 1
   ...> AND players.seasons_playerd > 21;
id    name           seasons_played  player_id  team_id  won_championship
----  -------------  --------------  ---------  -------  ----------------
1     Nolan Ryan      27             1          3        1

DELETE

Deleting from a table is done using the DELETE command. Above we made a mistake and inserted the New York Yankees into our teams table. None of the players we have in our database played for that team, so, like I wish we could in real life, we need to delete the Yankees.

First let’s get the Yankees' id:

sqlite> SELECT *
   ...> FROM teams;
id    name
----  -------------
1     Texas Rangers
2     Florida Marlins
3     New York Mets
4     California Angels
5     Milwaukee Brewers
6     New York Yankees

We’ll use their id, 6, to safely delete them from the database:

sqlite> DELETE FROM teams WHERE id = 6;
sqlite> SELECT * 
   ...> FROM teams;
id    name
----  -------------
1     Texas Rangers
2     Florida Marlins
3     New York Mets
4     California Angels
5     Milwaukee Brewers

I like that result set much better!

GROUP BY

We can also group results together and aggregate values. We’ll list our teams and aggregate the total number of players in our database that spent one of their first two seasons playing there. This query will require us to GROUP BY team name and aggregate the number of players using the COUNT function. We’ll count the number of players on a team by counting the number of players.name values in each group:

sqlite> SELECT teams.name, COUNT(players.name)
   ...> FROM teams
   ...> INNER JOIN players_teams ON teams.id = players_teams.team_id
   ...> INNER JOIN players ON players.id = players_teams.player_id
   ...> GROUP BY teams.name;
name               COUNT(players.name)
----               -----------------
California Angels  1
Florida Marlins    1
Milwaukee Brewers  1
New York Mets      1
Texas Rangers      2

Let’s look at what the result set would look like without grouping (we’ll order by team name to make the results more obvious):

sqlite> SELECT teams.name, players.name
   ...> FROM teams
   ...> INNER JOIN players_teams ON teams.id = players_teams.team_id
   ...> INNER JOIN players ON players.id = players_teams.player_id
   ...> ORDER BY teams.name;
name               players.name
----               -----------------
California Angels  Nolan Ryan
Florida Marlins    Ivan Rodriguez
Milwaukee Brewers  Jim Sundberg
New York Mets      Nolan Ryan
Texas Rangers      Jim Sundberg
Texas Rangers      Ivan Rodriguez

The Texas Rangers show up twice in the non-grouped result set.

One thing to note about the GROUP BY clause is anything we leave in the SELECT statement must either be aggregated, what we’re doing with the COUNT function, or in the GROUP BY clause, in this case teams.name.

There are many other types of aggregate functions we can use.Here is a list of aggregate functions available in Postgres.

HAVING

The HAVING clause works like a WHERE clause, but on grouped results sets. If we go back to our list of teams and player counts from above we can use a HAVING to limit the result set to only teams that have more than one player from our list on them in their first two seasons, or a COUNT(player.id) of greater than one:

sqlite> SELECT teams.name, COUNT(players.id)
   ...> FROM teams
   ...> INNER JOIN players_teams ON teams.id = players_teams.team_id
   ...> INNER JOIN players ON players.id = players_teams.player_id
   ...> GROUP BY teams.name
   ...> HAVING COUNT(players.id) > 1;
name           COUNT(players.id)
----           -----------------
Texas Rangers  2

Our result set has now been limited to only the teams we’re interested in.

Sub Queries

We can also embed queries in our queries to create more useful result sets. Let’s use a sub query to get a list of players who have won a championship:

sqlite> SELECT *
   ...> FROM players
   ...> INNER JOIN (
   ...>   SELECT player_id, MAX(won_championship)
   ...>   FROM players_teams
   ...>   GROUP BY player_id) sub_query_players_teams ON players.id = sub_query_players_teams.player_id;
id    name           seasons_played  player_id  MAX(won_championship)
----  -------------  --------------  ---------  ---------------------
1     Nolan Ryan      27             1          1
2     Jim Sundberg    16             2          0
3     Ivan Rodriguez  21             3          1

In this case we’re just doing an INNER JOIN, but instead of joining to an existing table, we’re creating a new result set and joining to that. Our subquery consists of only a list of player ids and the max of all of their won_championship columns. In the case they have the max will be one otherwise it will be zero. Taking advantage of the sub query we’re able to get a nice list of players and whether they won a championship (the last column in our result set). If we tried to do this without a subquery we would have to include all the information from our players_teams table, which as we recall from our inital INNER JOIN would lead to the players in the result set being doubled. In addition we couldn’t say definitively if a player had won or not. We would be forced to look at several different rows to deduce the information.

What’s next?

If you found this useful, you might also enjoy: