Author: admin

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…

BETWEEN The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included. Example Select all products with a price between 10 and 15: Run Example » BETWEEN Text Values The BETWEEN operator can also be used on text values. The result returns all records…

IN The IN operator allows you to specify a list of possible values in the WHERE clause. The IN operator is a shorthand for multiple OR conditions. Example Return all customers from ‘Germany’, France’ or ‘UK’: Run Example » NOT IN By using the NOT keyword in front of the IN operator, you return all records that are NOT any of the values in the…

LIKE The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: Starts with To return records that starts with a specific letter or phrase, add the % at the end of the letter or phrase. Example Return all customers with a name that…