In my previous blog, I went over writing query statements, naming conventions for columns, formatting conventions for query statements, and also the LIMIT statement. In this blog, I will go over several more useful clauses that can be used and also show examples of them in use.
WHERE
The first clause I will go over is the WHERE clause. An example of a WHERE clause used in a query would be the one that was used at the end of the last blog.
SELECT *FROM ramenratingsWHERE stars = 5
The above query returns only the rows where the stars column is equal to five. In excel it is possible to reorder the column without reordering any other columns, but that would scramble the data. In SQL, the entire rows of data are preserved and returned. When you write a WHERE clause to filter based on a value in one column, the results are limited to only rows that meet the condition. SQL treats each row as one data point, and all the information in the row belonged together.
Comparison Operator
In the above statement, we set a condition that stars must equal five. This is a comparison operator for equal to. Below is a list of them:
- Equal to =
- Not Equal to <> or !=
- Greater than >
- Less than <
- Greater than equal to >=
- Less than equal to <=
Comparison operators make the most sense when used on a numerical column. = and != operators can be used along with a where statement to find matches or non-matches
SELECT * FROM ramenratings WHERE country = ‘South Korea’ AND style != ‘Cup’
In the above query, only ramens from South Korea, excluding cup ramens, are returned.
When other comparison operators are used on non-numeric columns, they are filtered based on alphabetical order.
SELECT * FROM ramenratings WHERE Country > ‘U’
For the above statements, only rows starting with U or later alphabets would be returned. You might have noticed that USA is also returned despite the operator not being >=, the reason is that SQL treats US as being bigger than U because it has an extra letter. Most dictionaries follow the same logic.
Arithmetic
Arithmetic operations can be used in SQL to perform arithmetic across columns on values in a given row. For example
SELECT price + star AS non_sense_num FROM ramenratings
If you want to add all the numbers in a column, an aggregate function would need to be used. They will be talked about in a later section.
SQL Logical Operator
Logical operators allow you to use multiple comparisons in one query. The following are SQL logical operators.
- LIKE — let you match similar values instead of exact values
- IN — let you search in a list that you want to allow for the condition.
- BETWEEN — let you set a range
- IS NULL — let you select rows that contain no values.
- AND — let you set multiple conditions that must be met.
- OR — let you set multiple conditions where only one must be met.
- NOT — let you select rows that do not meet a condition.
