Joining Text

Sometimes you want several values in one piece of text: a game's title with its genre, a customer's name with their email. The operator ||, two vertical bars, joins texts together:

SELECT title || ' (' || genre || ')'
FROM games;

' (' and ')' are texts written right in the query. In SQL, text always goes between single quotes (apostrophes): 'Hello', ' ('. The quotes aren't part of the text. They only show where it starts and where it ends, and everything between them stays exactly as it is, spaces and capital letters too. Without the quotes, SQLite would take a word for the name of a column. Numbers are written without quotes: 5, 0.8.

Typical mistake: the name of a column in quotes. 'city' isn't the column city, it's the text “city”, so SELECT name, 'city' FROM customers; shows the word city in every row. And double quotes are for names, like the alias "sale price" in the previous exercise, not for text. SQLite sometimes forgives text in double quotes, but only when no column has that name: "city" is still the column. Other databases don't forgive it at all.

|| joins numbers too, as text: title || ': $' || price gives Dragon Forge: $40. Careful with calculations, though: || goes before * and -, so title || ': $' || price * 2 first joins the texts and then multiplies the whole text by 2, which makes no sense (the result is 0). Put the calculation in parentheses: title || ': $' || (price * 2).

If any of the joined values is NULL, the whole result is NULL. Ava Green has no city, so for her, this query gives NULL, not “Ava Green from”:

SELECT name || ' from ' || city AS customer
FROM customers;

You'll learn how to put another value in place of NULL in lesson 4. And as you can see, a joined column usually gets an alias; the calculation itself would make a long name. One more note: || works in SQLite, PostgreSQL and Oracle, while MySQL and SQL Server join texts with the function CONCAT().