Category: Postgresql

CASE The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). Once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.…

ALL The ALL operator: ALL means that the condition will be true only if the operation is true for all values in the range. Example List the products if ALL the records in the order_details with quantity larger than 10. Note: This will of course return FALSE because the quantity column has many different values (not only the value…

HAVING The HAVING clause was added to SQL because the WHERE clause cannot be used with aggregate functions. Aggregate functions are often used with GROUP BY clauses, and by adding HAVING we can write condition like we do with WHERE clauses. Example List only countries that are represented more than 5 times: Run Example » More HAVING Examples The following SQL statement lists only orders…

GROUP BY The GROUP BY clause groups rows that have the same values into summary rows, like “find the number of customers in each country”. The GROUP BY clause is often used with aggregate functions like COUNT(), MAX(), MIN(), SUM(), AVG() to group the result-set by one or more columns. Example Lists the number of customers in each country: Run Example » GROUP BY With…

FULL JOIN The FULL JOIN keyword selects ALL records from both tables, even if there is not a match. For rows with a match the values from both tables are available, if there is not a match the empty fields will get the value NULL. Let’s look at an example using our dummy testproducts table:  testproduct_id |      product_name      | category_id—————-+————————+————-             …

RIGHT JOIN The RIGHT JOIN keyword selects ALL records from the “right” table, and the matching records from the “left” table. The result is 0 records from the left side if there is no match. Let’s look at an example using our dummy testproducts table:  testproduct_id |      product_name      | category_id—————-+————————+————-              1 | Johns Fruit Cake       |           3              2 |…

LEFT JOIN The LEFT JOIN keyword selects ALL records from the “left” table, and the matching records from the “right” table. The result is 0 records from the right side if there is no match. Let’s look at an example using our dummy testproducts table:  testproduct_id |      product_name      | category_id—————-+————————+————-              1 | Johns Fruit Cake       |           3              2 |…

INNER JOIN The INNER JOIN keyword selects records that have matching values in both tables. Let’s look at an example using our dummy testproducts table:  testproduct_id |      product_name      | category_id—————-+————————+————-              1 | Johns Fruit Cake       |           3              2 | Marys Healthy Mix      |           9              3 | Peters Scary Stuff     |          10              4 | Jims Secret Recipe     |          11              5…

JOIN A JOIN clause is used to combine rows from two or more tables, based on a related column between them. Let’s look at a selection from the products table:  product_id |  product_name  | category_id————+—————-+————-         33 | Geitost        |           4         34 | Sasquatch Ale  |           1         35 | Steeleye Stout |           1         36 | Inlagd Sill    |           8 Then, look at…

Aliases SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword. Example Using aliases for columns: Run Example » AS is Optional…