| Operator | Description |
|---|---|
| = | Equal |
| > | Greater than |
| < | Less than |
| > = | Greater than or equal |
| < = | Less than or equal |
| < > or ! = | Not equal to |
| AND | Check if all values are true |
| OR | Check if at least one value is true |
| NOT | Returns the opposite of its following condition |
| Names | Description | Syntax | Query |
|---|---|---|---|
| SELECT | Select column | SELECT column FROM table; | SELECT * FROM user; |
| FROM | Indicate table | SELECT column FROM table; | SELECT * FROM user; |
| DISTINCT | Get unique values from column | SELECT DISTINCT column FROM table; | SELECT COUNT(DISTINCT rating) FROM film; |
| WHERE | Specifies conditions on columns for the rows to be turned | SELECT column FROM table WHERE condition; | SELECT email FROM customer WHERE first_name = 'Nancy' AND last_name = 'Thomas'; |
| ORDER BY | Order table by column | SELECT column_1,column_2 FROM table ORDER BY column_1 ASC / DESC; | SELECT employee FROM table ORDER BY company ASC,sales DESC,employee ASC; |
| LIMIT | Set the max number of rows | SELECT column FROM table ... LIMIT number_of_rows; | SELECT payment FROM employee ORDER BY payment_date DESC LIMIT 5; |
| BETWEEN | Get values between a low and a high. It's the same as value > = low AND value < = high. Obs.: when you are dealing with dates, you need to be aware that it starts and finishes counting on 00:00h | SELECT column FROM table BETWEEN low AND high; | SELECT payment FROM employee BETWEEN '2007-01-01' AND '2007-02-01'; |
| IN | Checks if a value is included in a list of multiple conditions. It's a short syntax for multiple OR statements | SELECT column FROM table WHERE IN(condition_1, condition_2, ...) ; | SELECT tint FROM shop WHERE tone IN('red','blue','pink'); |
| GROUP BY | Aggregates columns per some category | SELECT category_col, AGG(data_col) FROM table GROUP BY category_col; | SELECT customer_id, company, SUM(amount) FROM payment GROUP BY customer_id, company ORDER BY SUM(amount) DESC; |
| HAVING | It works as a second WHERE, after GROUP BY using an aggregate function | SELECT category_col, AGG(data_col) FROM table WHERE condition GROUP BY category_col HAVING condition_with_agg; | SELECT company, SUM(sales) FROM finance_table WHERE company ! = 'Google' GROUP BY company HAVING SUM(sales) > 1000 |
| AS | It renames some column | SELECT column AS new_column_name FROM table; | SELECT AVG(amount) AS average_salary FROM payment; |
The LIKE and ILIKE operators allow us to perform pattern matching against string data with the use of wildcard characters
| Cases | Query |
|---|---|
| All names that begin with an 'A' | WHERE name LIKE 'A%'; |
| All email that ends with 'gmail.com' or 'GMAIL.COM' | WHERE email ILIKE '%gmail.com'; |
| Return Cheryl, Theresa and Sherri | WHERE name LIKE '_her%'; |
| Name | Description | Query |
|---|---|---|
| AVG | The average of all the values | SELECT AVG(price) FROM movie; |
| COUNT | The number of rows | SELECT COUNT(*) FROM table; |
| MAX | The maximum value | SELECT MAX(price) FROM movie; |
| MIN | The minimum value | SELECT MIN(price) FROM movie; |
| SUM | The sum of all values | SELECT SUM(price) FROM movie; |
| ROUND | Round some value | SELECT ROUND(AVG(price), 2) FROM movie; |
| Name | Description | Syntax | Query | Query result | Usefull queries |
|---|---|---|---|---|---|
| INNER JOIN or JOIN | Returns all rows from tables where the key record of one table is equal to the key records of another table | SELECT * FROM table1 INNER JOIN table2 ON table1.col_to_match = table2.col_to_match; | SELECT * FROM registrations JOIN logins ON registrations.email = logins.email; | A table with all customers who have already loged in | ... |
| FULL OUTER JOIN or OUTER JOIN | Returns all records when there is a match in left (table1) or right (table2) table records filling blank spaces with null. | SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.col_to_match = table2.col_to_match; | SELECT * FROM registrations OUTER JOIN logins ON registrations.email = logins.email; | A table with all registrations and logins, filling with null logins with clients not registered and registrations that have never loged in | Opposite result of INNER JOIN: SELECT * FROM table1 OUTER JOIN table2 ON table1.col_to_match = table2.col_to_match WHERE table1.id IS null OR table2.id IS null; |
| LEFT OUTER JOIN or LEFT JOIN | Returns all records from the left table (table1), and the matched records from the right table (table2). The result is null from the right side, if there is no match. | SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.col_to_match = table2.col_to_match; | SELECT * FROM registrations LEFT JOIN logins ON registrations.email = logins.email; | A table with all registrations with their login data, filling with null registrations that have never loged in | Only unique entries from table1 (inside table1 and not found in table2): SELECT * FROM table1 LEFT JOIN table2 ON table1.col_to_match = table2.col_to_match WHERE table2.id IS null; |
| RIGHT OUTER JOIN or RIGHT JOIN | Returns all records from the right table (table2), and the matched records from the left table (table1). The result is null from the left side, when there is no match. | SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.col_to_match = table2.col_to_match; | SELECT * FROM registrations RIGHT JOIN logins ON registrations.email = logins.email; | A table with all logins with their registrations data, filling with null logins that have never being registered | Only unique entries from table2 (inside table2 and not found in table1): SELECT * FROM table1 RIGHT JOIN table2 ON table1.col_to_match = table2.col_to_match WHERE table1.id IS null; |
| UNION | Directly concatenate two results together | SELECT column_a FROM table1 UNION SELECT column_b FROM table2; | SELECT * FROM sales2021_q1 UNION SELECT * FROM sales2021_q2; | A table from 1st quarter of 2021 sales merged with 2nd quarter of 2021 sales | ... |
💡
Something you should be aware is that you don't actually need to use the RIGHT JOIN, you can always use only LEFT JOIN just specifying a different table order.
Obtains a sub-component of a date value
Ex.: SELECT EXTRACT(YEAR FROM date_col) FROM table
Returns the current age given a timestamp
Ex.: SELECT AGE(date_col) FROM table → 13 years 1 mon 5 days 01:34:13.003423
Converts data types to text
Ex.: SELECT TO_CHAR(date_col, 'mm-dd-yyyy') FROM table
Date/Time info from the official documentation
Mathematical Functions and Operators
String Functions and Operators