Selecting data
The first thing you need to learn is how to select data from SQL table. We will use the table countries from the Databases and Tables article, to demonstrate how to retrieve rows from table. To select all 3 columns and all the data in the table, you simply use the following SQL expression:
SELECT Country, Continent, Capital, Population
FROM Countries
The SQL SELECT keyword is followed by a list of columns you want to select. For example you might want to retrieve only the country name column and in this case you would use the following statement:
SELECT Country
FROM Countries
The result will look like this:
| Country |
| USA |
| Canada |
| Mexico |
| UK |
| Italy |
As you can see the SELECT statement is very flexible and you can retrieve as many columns as you like and in any order you want.
Another SQL keyword we used in our statements above was the FROM keyword, which tells the SQL engine which table we are going to retrieve data from.
If you want to select all columns from a table, there is shortcut syntax to do that:
SELECT *
FROM Countries
We do not recommend using it though, because your SQL statements will be harder to read and you might run into performance issues as well.
Selecting data can be even more flexible if we use the WHERE SQL clause to further limit the number of rows we retrieve. The WHERE keyword is followed by search criteria, which defines which rows exactly to retrieve. For example if you want to select only European countries you can do it the following way:
SELECT Country, Continent, Capital, Population
FROM Countries
WHERE Continent = ‘Europe’
You can have more than one search criteria after the WHERE clause, and this is done by joining the conditions with the AND keyword. To select all countries from North America with population greater than 50 million people, use the following statement:
SELECT Country, Continent, Capital, Population
FROM Countries
WHERE Continent = ‘North America’ AND Population > 50,000,000
The result will be:
| Country |
Continent |
Capital |
Population |
| USA |
North America |
Washington |
298,444,215 |
| Mexico |
North America |
Mexico |
107,449,525 |
You can use the DISTINCT clause along in a SELECT statement, to retrieve only unique records for certain column or columns. Using the Countries table again, we can select a list of all continents like this:
SELECT DISTINCT Continent FROM Countries