LIKE OPERATOR
The LIKE operator in SQL is used to search for a specified pattern in a
column. It is commonly used with WHERE to filter rows based on partial
matches.
SELECT * FROM Customers
WHERE City LIKE 's%';
Examples of matching city names:
Seattle
Sydney
San Diego
Stockholm
SELECT * FROM Customers
WHERE City LIKE '%s';
% at the beginning means any number of characters can come before s
No characters allowed after s
So this will return rows where the City name ends with the letter ‘s’
Examples of matching cities:
Dallas
Athens
Brussels
Kansas
SELECT *
FROM Customers
WHERE Country LIKE '%land%';
What the query does
It retrieves all rows from the Customers table where the Country column
contains the substring “land” anywhere in its value.
Examples of countries that would match:
Finland
Iceland
Poland
Switzerland
Netherlands (contains lands)
SELECT *
FROM Customers
WHERE Country NOT LIKE '%land%';
What the query does
It returns all rows from the Customers table where the Country column does
NOT contain the substring “land” anywhere in its value.
So it excludes countries such as:
Finland
Iceland
Poland
Switzerland
Netherlands
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';
What the query does
This query selects all rows from the Customers table where the City column
starts with either b, s, or p.
The pattern:
[bsp]%
means:
[bsp] → the first character must be b, s, or p
Example matches
Cities like:
Berlin
Boston
Barcelona
Stockholm
Sydney
Paris
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';
[a-c] → the first character of City must be a, b, or c
% → followed by anything
So it matches cities like:
Amsterdam
Atlanta
Berlin
Bogotá
Copenhagen
Chicago
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';
[!bsp] → the first character must NOT be b, s, or p
So it returns all customers whose city does not begin with:
b
s
p
Example included cities:
Amsterdam
Cairo
London
Rome
Example excluded cities:
Berlin
Boston
Stockholm
Sydney
Paris
Prague
Basic Examples
1. Match values that start with something
SELECT *
FROM employees
WHERE name LIKE 'A%';
Explanation:
Returns all employees whose names start with 'A' (e.g., Alice, Adam, Angela).
2. Match values that end with something
SELECT *
FROM customers
WHERE email LIKE '%[Link]';
Explanation:
Finds all customers with Gmail email addresses.
3. Match values containing a substring
SELECT *
FROM products
WHERE description LIKE '%organic%';
Explanation:
Finds all products whose description contains the word "organic".
4. Match a single character with _
SELECT *
FROM users
WHERE username LIKE '_at';
Explanation:
Matches 3-letter usernames where the last two letters are at
(e.g., cat, bat, mat).
What is an INNER JOIN?
An INNER JOIN returns only the rows that have matching values in both
tables.
If a row in one table does not have a matching row in the other table, it is
excluded from the result.
Example Tables
Customers
CustomerID CustomerName Country
1 Alice USA
2 Bob UK
3 Carol USA
Orders
OrderID CustomerID Product
101 1 Laptop
102 3 Phone
103 4 Tablet
Example 1: Basic INNER JOIN
SELECT [Link], [Link]
FROM Customers
INNER JOIN Orders
ON [Link] = [Link];
Step 1:
Customers → contains customer information
Orders → contains order information
Step 2:
INNER JOIN Orders ON [Link] = [Link]
Meaning: “Combine rows from Customers and Orders only where the CustomerID
matches in both tables.”
Only customers who have placed orders will appear (because it’s an INNER JOIN).
Step 3:
[Link] → the name of the customer
[Link] → the product they ordered
Step 4:
“Show me the customer names and the products they ordered for all customers who have
at least one order.”
Example Result
CustomerName Product
Alice Laptop
Carol Phone
Bob is not included because he has no orders.
✔ What this returns:
Only customers who have orders:
CustomerName Product
Alice Laptop
Carol Phone
🔹 Bob (CustomerID 2) is not shown → because he has no orders
🔹 Order 103 is not shown → because CustomerID 4 doesn't exist in Customers
What is an OUTER JOIN?
An OUTER JOIN returns all rows from one or both tables, even when there
is no matching row in the other table.
There are three types:
1. LEFT OUTER JOIN
2. RIGHT OUTER JOIN
3. FULL OUTER JOIN
🟦 Example Tables
Customers
CustomerID CustomerName
1 Alice
2 Bob
3 Carol
Orders
OrderID CustomerID Product
101 1 Laptop
102 3 Phone
103 4 Tablet
🟩 1. LEFT OUTER JOIN (most common)
Returns all rows from the left table, plus matches from the right table.
If there's no match, NULL is used.
Query:
SELECT [Link], [Link]
FROM Customers
LEFT JOIN Orders
ON [Link] = [Link];
Step 1: Identify the tables
Customers → contains customer information
Orders → contains order information
Step 2: Identify the JOIN
LEFT JOIN Orders ON [Link] = [Link]
Meaning: “Take all rows from the Customers table and match them with rows
from Orders where the CustomerID matches. If a customer has no order, still include
the customer, but show NULL for the order.”
Step 3:
[Link] → the name of the customer
[Link] → the product they ordered (or NULL if they didn’t order anything)
Step 4:
“Show me the names of all customers and the products they ordered. If a customer has not
placed any orders, still show their name, but leave the product as NULL.”
Example Result
CustomerName Product
Alice Laptop
Bob NULL
Carol Phone
Bob appears even though he has no orders.
Alice and Carol appear with their orders.
Explanation:
Bob appears even though he has no order → Product = NULL
Order 103 (CustomerID 4) is ignored because it has no matching
customer
🟩 2. RIGHT OUTER JOIN
Returns all rows from the right table, plus matches from the left table.
If no match → NULL.
Query:
SELECT [Link], [Link]
FROM Customers
RIGHT JOIN Orders
ON [Link] = [Link];
Result:
CustomerName Product
Alice Laptop
Carol Phone
NULL Tablet
Explanation:
OrderID 103 has CustomerID 4 → no matching customer, so
CustomerName = NULL.
🟩 3. FULL OUTER JOIN
Returns all rows from both tables.
Matches show normally; non-matching rows show NULLs.
Query:
SELECT [Link], [Link]
FROM Customers
FULL OUTER JOIN Orders
ON [Link] = [Link];
Result:
CustomerName Product
Alice Laptop
Carol Phone
Bob NULL
NULL Tablet
Explanation:
Bob → no order → Product = NULL
Order 103 → no customer → CustomerName = NULL
Others match normally