The code that make up a line of SQL syntax is referred to as a statement. An SQL statement is formed from one or more keywords mixed with data. Each keyword is a reserved word that the database interpenetrates in order to execute a specific function. Specifically, the SELECT statement is referred to as a query because it queries information from a database.
The SELECT keyword queries information from the database and outputs the results.
The SELECT keyword requires several additional keywords to function as a valid statement. The first bit of information preceding the SELECT keyword specifies the column(s) to select. The SELECT statement requires that their exist a FROM clause in order to specify where the information should be retrieved from. The information preceding the FROM keyword specifies the table(s) to select from.
SELECT <column1> FROM <table1>;
You can select multiple columns and/or tables using a comma between objects:
SELECT <column1>, <column2> FROM <table1>, <table2>;
To select all of the columns from a single table, use the wildcard character (*):
SELECT * FROM <table1>;
SELECT * FROM ex_pets;:
| id | species | type | color | name |
|---|---|---|---|---|
| 1 | dog | dachshund | brown | Oliver |
| 2 | cat | british_shorthair | gray | Tiger |
| 3 | cat | birman | brown_white | Silverbell |
| 4 | dog | poodle | black | Annie |
| 5 | guinea_pig | american | black_white | Susie |
SELECT species, name FROM ex_pets;:
| species | name |
|---|---|
| dog | Oliver |
| cat | Tiger |
| cat | Silverbell |
| dog | Annie |
| guinea_pig | Susie |
A normal SELECT statement will return all results even if some of the rows have the exact same value. Take the result of SELECT species FROM ex_pets; for example:
| species |
|---|
| dog |
| cat |
| cat |
| dog |
| guinea_pig |
Because there are two rows with the species cat, they are both returned. With the DISTINCT keyword, only one row for cat will return:
SELECT DISTINCT species FROM ex_pets;
| species |
|---|
| dog |
| cat |
| dog |
| guinea_pig |
The WHERE clause allows you to query results based off of a condition.
The WHERE clause is added at the end of a SELECT statement to specify a condition that must be true in order for the database to return any results. There are several operators that you can use to match a result (<, >, ,=, <=, >=, !=). Each one will return the results differently. Keep in mind that only the equal sign (=) and not-equal sign (!=) operators can apply to strings. As an example you can use a WHERE clause to match a value:
SELECT * FROM <table1> WHERE <column1> = 'VALUE';
Or suppose you have a column with a numerical value and you only want to return rows with a higher value:
SELECT * FROM <table1> WHERE <column2> > 10;
SELECT species, type, name FROM ex_pets WHERE name = 'Silverbell';:
| species | type | name |
|---|---|---|
| cat | birman | Silverbell |
AND and OR keywords can further extend the WHERE condition in a SELECT statement. The AND clause adds an additional parameter that only returns data when all conditions are true. Alternatively, the OR clause returns the data when any conditions are true.
SELECT * FROM ex_pets WHERE species = 'dog' AND type='dachshund';:
| id | species | type | color | name |
|---|---|---|---|---|
| 1 | dog | dachshund | brown | Oliver |
SELECT * FROM ex_pets WHERE species = 'dog' OR type= 'poodle';:
| id | species | type | color | name |
|---|---|---|---|---|
| 1 | dog | dachshund | brown | Oliver |
| 4 | dog | poodle | black | Annie |
The NOT clause reverses the statement and returns all of the results that are false.
The IN keyword is a shortcut to test multiple values of a column without using the OR clause. When using the OR keyword, an entire expression must be used in order to test multiple conditions:
SELECT * FROM <table1> WHERE <column1> = <value1> OR <column1> = <value2>;
As and alternative, you can use IN to test both <value1> OR <value2> in a single condition:
SELECT * FROM <table1> WHERE <column1> IN (<value1>, <value2>);
The NOT IN keyword pair negates the IN condition. In other words, when adding the NOT keyword to an IN condition, the database returns what is false.
SELECT * FROM <table1> WHERE <column1> NOT IN (<value1>, <value2>);
The BETWEEN keyword is a shortcut for querying results that are between two values inclusively. The long way to do this would be to have an AND condition that tests if the value is greater than one number and less than another.
SELECT * FROM <table1> WHERE <column2> >= <value1> AND <column2> <= <value2>;
The BETWEEN keyword still uses the AND keyword, but can greatly shorten the query above:
SELECT * FROM <table1> WHERE <column2> BETWEEN <value1> AND <value2>;
The BETWEEN keyword works on both number type data as well as dates. It also works with strings, making it more powerful than using normal operators. It tests each string and returns the rows that are between the two values alphabetically.
Like the NOT IN condition, The NOT BETWEEN keyword pair negates the BETWEEN condition.
SELECT * FROM <table1> WHERE <column2> NOT BETWEEN <value1> AND <value2>;
In a lot of cases, you will have a table with NULL values (I.e: values that do not contain any data). You can search for records that have a NULL value using the keyword pair: IS NULL.
SELECT * FROM <table1> WHERE <column1> IS NULL;
Oppositely, you can use the IS NOT NULL keyword pair to only find records that do not have a NULL value (I.e: Only values that contain data).
SELECT * FROM <table1> WHERE <column1> IS NOT NULL;
Keep in mind that an empty string is different than a NULL.
The LIKE keyword is a way to search for strings based off of a pattern. There are several special operators that are used in LIKE patterns, but the syntax is like this:
SELECT * FROM <table1> WHERE <column1> LIKE '<pattern>';
% operator is a wildcard indicating one or more characters._ operator is a wildcard indicating only one character.Here are some of the operators that you can use in the LIKE pattern:
| Operator | Description |
|---|---|
| 'a%' | Finds any values that start with "a" |
| '%a' | Finds any values that end with "a" |
| '%or%' | Finds any values that have "or" in any position |
| '_r%' | Finds any values that have "r" in the second position |
| 'a__%' | Finds any values that start with "a" and are at least 3 characters in length |
| 'a%o' | Finds any values that start with "a" and ends with "o" |
You can change the appearance of a column name by using an alias, which is essentially a nickname that represents a real column. Declaring aliases is as simple as adding an AS keyword with another name after each specified column in the SELECT clause:
SELECT <column1> AS "<column1Alias>" FROM <table1>;
The symbol above is referred to as “two pipes” which is the keyword for concatenation in SQL. Concatenation is the combination of two or more strings put together. In SQL you can use concatenation to return a custom string of results. Each “string part” can contain a column name or a literal string.
SELECT <stringPart1> || <stringPart2> FROM <table1>;
SELECT name || ' is a ' || species AS "The Pets" FROM ex_pets;:
| The Pets |
|---|
| Oliver is a dog |
| Tiger is a cat |
| Silverbell is a cat |
| Annie is a dog |
| Susie is a guinea_pig |
The ORDER BY keyword can sort a query of results alphanumerically based on a column. Note that all of the other data within each row will stay with their sorted columns, meaning that the sort affects all columns in a specified table.
SELECT * FROM <table1> ORDER BY <column1>;
In Oracle, you can specify a number instead of a column name. The number specifies the position of the respective column you listed in the SELECT statement, or the position of the column within the table structure.
SELECT <column2>, <column3> FROM <table1> ORDER BY 2;
This will order by <column3>.
You can sort multiple columns at the same time by adding other column names after the ORDER BY keyword. This action will sort the first column first and then the second column after, and repeat for any other columns after that. Should the first column return multiple records with the same value, the second column will order the records alphanumerically by its own data.
SELECT * FROM <table1> ORDER BY <column1>, <column2>;
In the case that you would like the column to be sorted in descending order, you can add DESC after the column name to sort the records in this format.
SELECT * FROM <table1> ORDER BY <column1> DESC;
By default, the ORDER BY keyword will sort the column in ascending order, but if you wish, you can add ASC after the column name to make it clear that the results should be displayed in this format.
You can combine multiple columns to order by in different directions if you needed to.
SELECT * FROM <table1> ORDER BY <column1> ASC, <column2> DESC;
SELECT id, name, species FROM ex_pets ORDER BY name;:
| id | name | species |
|---|---|---|
| 4 | Annie | dog |
| 1 | Oliver | dog |
| 3 | Silverbell | cat |
| 5 | Susie | guinea_pig |
| 2 | Tiger | cat |
SELECT id, species, type FROM ex_pets ORDER BY species, type;:
| id | species | type |
|---|---|---|
| 3 | cat | birman |
| 2 | cat | british_shorthair |
| 1 | dog | dachshund |
| 4 | dog | poodle |
| 5 | guinea_pig | american |
The GROUP BY keyword pair is a handy shortcut for combining several independent SELECT DISTINCT statements into a single query. The GROUP BY clause will return results for all of the of the columns, instead of a condition based on a single column. Because of this, the GROUP BY is essential for group functions to work for an entire table. The WHERE clause cannot process Group Functions. GROUP BY identifies subsets of rows within the larger set of rows being considered by the SELECT statement. In this way, it’s sort of like creating a series of mini-SELECT statements within the larger SELECT statement.
SELECT <column1>, <column2> FROM <table1> GROUP BY <column2>;
SELECT species, count(species) FROM ex_pets GROUP BY species;:
| species | count(species) |
|---|---|
| cat | 2 |
| dog | 2 |
| guinea_pig | 1 |
Generally the GROUP BY is used to specify columns in the table that will contain common data in order to group rows together for performing some sort of aggregate function on the set of rows.
Keep in mind that the WHERE clause can still be used in a “grouped” statement. It will further filter the results the the GROUP BY clause will return.
SELECT species, count(species) FROM ex_pets WHERE type != 'dachshund' GROUP BY species;:
| species | count(species) |
|---|---|
| cat | 2 |
| dog | 1 |
| guinea_pig | 1 |
The HAVING keyword allows you to add a condition to a GROUP BY clause. It is essentially the equivalent of using the WHERE keyword in a statement that uses a group function. The difference is, the HAVING keyword will not cause a syntax error.
SELECT <column1>, <column2> FROM <table1> GROUP BY <column2>
HAVING <column1> = 'VALUE';
SELECT species FROM ex_pets GROUP BY species HAVING count(species) = 2:
| species |
|---|
| cat |
| dog |
The following keywords must be written in this order for the database to understand them:
You can query the address name in the table using the ROWID as a column name. This will return a column of address identification values for each row that is returned. This is technically called a Pseudo Column.
SELECT ROWID, <column1> FROM <table1>;
In PostgreSQL, you can show the structure of a table by using the \d command.
\d <table>;