For example, lets say you have a table named 'users' that contains a list of clients. If you execute the following SQL, it will return all of the rows in the table:
If you want to find only rows where the user's first name is 'john', you would add a WHERE clause condition that tells the database to match only those rows:
To find all rows where the first name is NOT 'john', you could use the "not equal" operator, as shown in this SQL:
(The '<>' means 'NOT EQUAL TO'.)
To find all rows where the first name is 'john' and the city is 'Boston', you could use this query:
Multiple conditions can be added to the WHERE clause, including sub-queries that can contain their own WHERE clauses. A sub-query is basically a query within a query. They are also called "nested queries" or "sub-selects". To be valid, all sub-queries must be enclosed in parentheses.
There are 3 basic types of sub-queries in SQL, and sub-queries in the WHERE clause are referred to as "predicate" sub-queries. Predicate sub-queries can be used only in the HAVING and WHERE clauses, and these sub-queries must retrieve one column.
In the following example the sub-query is being used to retrieve records from the users table that are also listed in the admin table:
Related Code Snippets: