Operators Flashcards
INTERSECT
Combines two SELECT statements and returns only the dataset that is common in both the statements
EXCEPT
Returns the distinct rows that are present in the result set of the first query but not in the result set of the second query.
LIKE
The LIKE command is used in a WHERE clause to search for a specified pattern in a column.
You can use two wildcards with LIKE:
% - Represents zero, one, or multiple characters
_ - Represents a single character (MS Access uses a question mark (?) instead)
REGEXP
Used to search for a specified pattern using regular expression pattern matches.
UNION
Combines the data from the result of two or more SELECT statements into a single result set. This operator removes any duplicates present in the results being combined.
UNION ALL
Combines the data from the result of two or more SELECT statements into a single result set. This operator DOES NOT REMOVE any duplicates present in the results being combined.
IS NULL
Used to test for empty values (NULL values).
AND
Displays a record if all the conditions stated in the query are TRUE.
Example:
SELECT * FROM Customers
WHERE Country = ‘Germany’
AND City = ‘Berlin’
AND PostalCode > 12000;
This will display all Customers that are located in Berlin, Germany with a postal code 12000.
OR
Displays a record in which any of the conditions stated in the query are TRUE.
Example:
SELECT * FROM Customers
WHERE Country = ‘Germany’
OR City = ‘Berlin’
OR PostalCode > 12000;
This will display all Customers that are located in any city named Berlin (regardless of the country it’s in), customer in any city in Germany and customers that have their postal code as 12000.
=
Returns values that are “Equal To” the value input in the query
>
Returns values that are “Greater Than” the value input in the query
<
Returns values that are “Less Than” the value input in the query
> =
Returns values that are “Equal To or Greater Than” the value input in the query
<=
Returns values that are “Equal To or Less Than” the value input in the query
<>
Returns values that are “NOT Equal To” the value input in the query
%
This represents the “modulo”. It is used to get the remainder from a division.
IN
The IN operator allows you to specify multiple values in a WHERE clause. Example:
SELECT customerName, state
FROM customers
WHERE state IN (‘MA’, ‘CA’, ‘PA’)
This will return all of the customers that are registered in the database “customers” as located in the states of MA (Massachussets), CA (California) or PA (Pennsylvania).
NOT IN
The NOT IN operator will return all records that are NOT any of the values in the list in a WHERE clause.
Example:
SELECT customerName, country
FROM customers
WHERE state NOT IN (‘USA’, ‘France’, ‘Germany’)
This will return all of the customers that are registered in the database “customers” and are NOT located in USA, France or Germany.
CROSS JOIN
Used to generate a paired combination of each row of the first table with each row of the second table.
INNER JOIN
Selects records that have matching values in both tables.
When using a JOIN operator, if both tables are using the same field name what syntax could you use to call for that specific field in each table?
USING (nameofthefield);
When using a JOIN operator, if the fields on each table do NOT share the same name, what syntax should you use to call for that specific field from each table?
ON table1.nameofthefield = table2.nameofthefieldonthistable
Which table will LEFT JOIN retrieve the information from?
The first table
Which table will RIGHT JOIN retrieve the information from?
The second table
