Last updated on 2023-05-02 | Edit this page
- How can I filter data?
- Write queries that
SELECTdata based on conditions, such as
- Understand how to use the
WHEREclause in a statement.
- Learn how to use comparison keywords such as
LIKEin a statement.
SQL is a powerful tool for filtering data in databases based on a set
of conditions. Let’s say we only want data for a specific ISSN, for
instance, for the Acta Crystallographica journal from the
articles table. The journal has an ISSN code
2056-9890. To filter by this ISSN code, we will use the
SELECT * FROM articles WHERE ISSNs='2056-9890';
We can add additional conditions by using
NOT. For example, suppose we want
the data on Acta Crystallographica published after October:
SELECT * FROM articles WHERE (ISSNs='2056-9890') AND (Month > 10);
Parentheses are used merely for readability in this case but can be required by the SQL interpreter in order to disambiguate formulas.
If we want to get data for the Humanities and Religions journals, which have ISSNs codes “2076-0787” and “2077-1444”, we can combine the tests using OR:
SELECT * FROM articles WHERE (issns = '2076-0787') OR (issns = '2077-1444');
When you do not know the entire value you are searching for, you can
use comparison keywords such as
IS NULL. For instance, we can
LIKE in combination with
WHERE to search
for data that matches a pattern.
For example, using the
articles table again, let’s
SELECT all of the data
Subject contains “Crystal Structure”:
SELECT * FROM articles WHERE Subjects LIKE '%Crystal Structure%';
You may have noticed the wildcard character
%. It is
used to match zero to many characters. So in the SQL statement above, it
will match zero or more characters before and after ‘Crystal
Let’s see what variations of the term we got. Notice uppercase and lowercase, the addition of ‘s’ at the end of structures, etc.
To learn more about other comparison keywords you can use, see Beginner SQL Tutorial on SQL Comparison Keywords.
SELECT Title, First_Author, Subjects, ISSNs, Month, Year FROM articles WHERE (Subjects LIKE '%computer%') AND (Citation_Count > 8);
You can continue to add or chain conditions together and write more advanced queries.