Here's the overview of the document, outlining essential SQL functions critical for effective data querying and manipulation.
- Equals (
=
) - Not Equals (
!=
) - Not Equal To (
<>
) - Like (
LIKE
) - Not Like (
NOT LIKE
) - In (
IN
) - Not In (
NOT IN
) - Greater Than (
>
) - Less Than (
<
) - Greater Than or Equal To (
>=
) - Less Than or Equal To (
<=
) - Between (
BETWEEN
)
'=': Specifies an exact match condition in a dataset.
Keyword | Example |
---|---|
Equals | 1. Find records where the quantity equals 10. 2. Display employees whose tenure equals 5 years. |
Is the same as | 1. List products whose price is the same as $20. 2. Identify days when the output is the same as the target. |
Matches | 1. Show orders that matches the specified ID. 2. Filter dates that matches the event day. |
Is identical to | 1. Retrieve transactions that are identical to the sample. 2. Filter items in inventory that are identical to the order. |
Is equivalent to | 1. Search for codes that are equivalent to the entered value. 2. Find the process status that is equivalent to 'Completed'. |
Corresponds to | 1. Show accounts that corresponds to the given user. 2. Display products whose code corresponds to the input. |
Is alike | 1. Filter results where the outcome is alike the predicted value. 2. List employees whose performance is alike the standard. |
Is equal to | 1. Identify departments whose budget is equal to $10000. 2. Find entries where the count is equal to 200. |
Is | 1. Locate documents where the status is 'Approved'. 2. Show instances where the temperature is 32 degrees. |
Coincides with | 1. Find events that coincides with the selected date. 2. Display records where the delivery coincides with the due date. |
In of | 1. Show colors in of the selected palette. 2. List materials in of the specified type. |
By as a filter | 1. Filter products by as a filter of category 'Electronics'. 2. Sort results by as a filter of highest rating. |
With a | 1. Display rows with a status of 'Active'. 2. Filter by items with a quantity of 50. |
Set to | 1. Retrieve settings where the mode is set to 'Auto'. 2. Show users whose access is set to 'Administrator'. |
'<>': Identifies records where values do not match a specified criterion.
Keyword | Example |
---|---|
Does not equal | 1. Find records where the quantity does not equal 10. 2. Display employees whose tenure does not equal 5 years. |
Is not the same as | 1. List products whose price is not the same as $20. 2. Identify days when the output is not the same as the target. |
Does not match | 1. Show orders that do not match the specified ID. 2. Filter dates that do not match the event day. |
Is different from | 1. Retrieve transactions that are different from the sample. 2. Filter items in inventory that are different from the order. |
Is not identical to | 1. Search for codes that are not identical to the entered value. 2. Find the process status that is not identical to 'Completed'. |
Is not equivalent to | 1. Display accounts that are not equivalent to the given user. 2. Show products whose code is not equivalent to the input. |
Differs from | 1. Filter results where the outcome differs from the predicted value. 2. List employees whose performance differs from the standard. |
Is unlike | 1. Identify departments whose budget is unlike $10000. 2. Find entries where the count is unlike 200. |
Is not | 1. Locate documents where the status is not 'Approved'. 2. Show instances where the temperature is not 32 degrees. |
Does not coincide with | 1. Find events that do not coincide with the selected date. 2. Display records where the delivery does not coincide with the due date. |
'LIKE': Used to search for a specified pattern in a column.
Keyword | Example |
---|---|
Similar to | 1. Find names similar to 'John'. 2. Display products similar to 'milk'. |
Like | 1. Search for emails like '%@gmail.com'. 2. List titles like 'Mr.%'. |
Resembles | 1. Identify addresses that resemble 'Main St%'. 2. Show codes that resemble 'AB%'. |
Kind of | 1. Find descriptions kind of 'sweet%'. 2. Locate names kind of 'Geo%'. |
Roughly | 1. Search for dates roughly '2020%'. 2. Display amounts roughly '100%'. |
Approximately | 1. Identify products priced approximately '$50%'. 2. Find quantities approximately '10%'. |
Sort of | 1. List employees with names sort of 'Al%'. 2. Show items in categories sort of 'book%'. |
In the manner of | 1. Find transactions in the manner of 'Online%'. 2. Display customers in the manner of 'VIP%'. |
Mimics | 1. Search for patterns that mimic 'XX%'. 2. List products that mimic 'Type A%'. |
Nearly | 1. Identify projects nearly 'Complete%'. 2. Show locations nearly 'Downtown%'. |
Starts with | 1. Find cities that start with 'New'. 2. List products that start with 'Choco'. |
Ends with | 1. Search for usernames that end with 'smith'. 2. Display emails that end with 'http://company.com/'. |
Contains | 1. Identify comments that contain 'urgent'. 2. Show records that contain 'confidential'. |
Has | 1. Find records that have 'pending' in status. 2. List descriptions that have 'discount'. |
Begins with | 1. Locate orders that begin with 'A'. 2. Display names that begin with 'Mar'. |
'NOT LIKE': Used to exclude rows that match a specified pattern.
Keyword | Example |
---|---|
Not containing | 1. Find emails not containing '@company.com'. 2. List products not containing 'gluten'. |
Excluding | 1. Display names excluding 'John'. 2. Show orders excluding 'online'. |
Lacking | 1. Identify records lacking 'complete' status. 2. Locate items lacking 'warranty'. |
Not starting with | 1. Find cities not starting with 'New'. 2. List emails not starting with 'info'. |
Not ending with | 1. Search for usernames not ending with 'smith'. 2. Display products not ending with 'free'. |
Without | 1. Show records without 'approved' status. 2. List descriptions without 'discount'. |
Missing | 1. Find entries missing 'address' details. 2. Identify customers missing 'email'. |
Not including | 1. Display categories not including 'electronics'. 2. Show transactions not including 'refund'. |
Is not like | 1. Locate products that are not like 'Book%'. 2. Identify emails that are not like '%@gmail.com'. |
Not having | 1. Find accounts not having 'active' status. 2. List orders not having 'tracking number'. |
'IN': Used to select rows where a value matches any value in a list or subquery.
Keyword | Example |
---|---|
Among | 1. Find products among 'electronics', 'furniture', 'clothing'. 2. Identify employees among 'Sales', 'Marketing', 'IT'. |
Included in | 1. List countries included in 'USA', 'UK', 'Canada'. 2. Show orders included in 'Completed', 'Shipped', 'Processing'. |
Part of | 1. Display items part of 'Summer Collection', 'Winter Sale'. 2. Locate accounts part of 'Premium', 'Standard', 'Basic'. |
Within | 1. Find cities within 'California', 'Texas', 'Florida'. 2. Identify transactions within 'Q1', 'Q2', 'Q3'. |
In the list of | 1. Show products in the list of 'Bestsellers', 'New Arrivals'. 2. List employees in the list of 'Team Leads', 'Managers'. |
In the range of | 1. Find prices in the range of '50', '100', '150'. 2. Locate ages in the range of '20', '30', '40'. |
Amongst | 1. Display orders amongst 'Online', 'In-Store', 'Phone'. 2. Identify colors amongst 'Red', 'Blue', 'Green'. |
Inside | 1. List categories inside 'Home Appliances', 'Garden', 'Books'. 2. Show dates inside 'January', 'February', 'March'. |
In the group of | 1. Find names in the group of 'Emma', 'Olivia', 'Ava'. 2. Locate departments in the group of 'HR', 'Finance', 'Operations'. |
In the set of | 1. Display products in the set of 'Limited Edition', 'Exclusive'. 2. Identify services in the set of 'Cleaning', 'Maintenance', 'Repair'. |
'NOT IN': Used to exclude rows where a value matches any value in a list or subquery.
Keyword | Example |
---|---|
Not among | 1. Find products not among 'electronics', 'furniture', 'clothing'. 2. Identify employees not among 'Sales', 'Marketing', 'IT'. |
Not included in | 1. List countries not included in 'USA', 'UK', 'Canada'. 2. Show orders not included in 'Completed', 'Shipped', 'Processing'. |
Outside of | 1. Display items outside of 'Summer Collection', 'Winter Sale'. 2. Locate accounts outside of 'Premium', 'Standard', 'Basic'. |
Excluded from | 1. Find cities excluded from 'California', 'Texas', 'Florida'. 2. Identify transactions excluded from 'Q1', 'Q2', 'Q3'. |
Not part of | 1. Show products not part of 'Bestsellers', 'New Arrivals'. 2. List employees not part of 'Team Leads', 'Managers'. |
Not within | 1. Find prices not within '50', '100', '150'. 2. Locate ages not within '20', '30', '40'. |
Not in the list of | 1. Display orders not in the list of 'Online', 'In-Store', 'Phone'. 2. Identify colors not in the list of 'Red', 'Blue', 'Green'. |
Not in the range of | 1. List categories not in the range of 'Home Appliances', 'Garden', 'Books'. 2. Show dates not in the range of 'January', 'February', 'March'. |
Outside the group of | 1. Find names outside the group of 'Emma', 'Olivia', 'Ava'. 2. Locate departments outside the group of 'HR', 'Finance', 'Operations'. |
Not in the set of | 1. Display products not in the set of 'Limited Edition', 'Exclusive'. 2. Identify services not in the set of 'Cleaning', 'Maintenance', 'Repair'. |
'>': Used to select rows where a value is greater than a specified value.
Keyword | Example |
---|---|
More than | 1. Find employees with salaries more than $50,000. 2. Identify products with more than 100 units in stock. |
Exceeding | 1. List days exceeding 24 hours of usage. 2. Show transactions exceeding $1,000 in value. |
Above | 1. Display temperatures above 30°C. 2. Locate accounts with balances above $5,000. |
Over | 1. Find employees over 40 years old. 2. Identify cars with mileage over 100,000 miles. |
Higher than | 1. List prices higher than $20. 2. Show speeds higher than 60 mph. |
Beyond | 1. Display dates beyond 2023. 2. Locate scores beyond 90 points. |
Greater than | 1. Find areas greater than 500 square feet. 2. Identify weights greater than 2 kg. |
Larger than | 1. List files larger than 1GB. 2. Show populations larger than 1 million. |
Surpassing | 1. Display heights surpassing 6 feet. 2. Locate distances surpassing 100 miles. |
In excess of | 1. Find donations in excess of $500. 2. Identify durations in excess of 2 hours. |
Longer than | 1. List movies longer than 2 hours. 2. Show paths longer than 5 miles. |
'BETWEEN': Selects rows where a value is within a specified range.
Keyword | Example |
---|---|
Greater than or equal to | 1. Find products with a price greater than or equal to $25. 2. Identify employees with tenure greater than or equal to 5 years. |
Within the range of | 1. Display dates within the range of January 1 to December 31. 2. Locate temperatures within the range of 20°C to 35°C. |
From...to... | 1. List salaries from $50,000 to $75,000. 2. Show products from category A to category E. |
Between | 1. Find ages between 20 and 30 years. 2. Identify scores between 60 and 80 points. |
In the span of | 1. Display records in the span of the last 5 years. 2. Locate projects in the span of January to March. |
Inside the limits of | 1. List quantities inside the limits of 100 to 500 units. 2. Show distances inside the limits of 5 to 10 kilometers. |
From...through... | 1. Find events from January through December. 2. Identify expenses from $1,000 through $5,000. |
Within the bounds of | 1. Display days within the bounds of Monday to Friday. 2. Locate weights within the bounds of 50kg to 100kg. |
In the interval of | 1. List times in the interval of 9:00 AM to 5:00 PM. 2. Show measurements in the interval of 10 meters to 20 meters. |
Lying between | 1. Find products lying between $10 and $50. 2. Identify speeds lying between 30 mph and 50 mph. |
Spanning from...to... | 1. Display areas spanning from 500 square feet to 1000 square feet. 2. Locate periods spanning from the Bronze Age to the Iron Age. |
'<': Used to select rows where a value is less than a specified value.
Keyword | Example |
---|---|
Less than | 1. Find products with a price less than $100. 2. Identify employees younger than 30 years. |
Smaller than | 1. List files smaller than 2MB. 2. Show areas smaller than 500 square feet. |
Shorter than | 1. Find movies shorter than 2 hours. 2. Locate tracks shorter than 3 minutes. |
Lighter than | 1. Display products lighter than 1kg. 2. Identify luggage lighter than 20kg for a flight. |
Younger than | 1. List candidates younger than 25 for a program. 2. Show animals younger than 1 year in the zoo. |
Slower than | 1. Find vehicles slower than 100 km/h. 2. Identify internet connections slower than 10 Mbps. |
Closer than | 1. Locate stores closer than 5 miles. 2. Show planets closer than 100 million miles to Earth. |
Poorer than | 1. Identify countries with a GDP poorer than $1 trillion. 2. List communities poorer than the national average income. |
Weaker than | 1. Find materials weaker than steel for construction. 2. Display signals weaker than -50 dBm. |
Cooler than | 1. Locate days cooler than 20°C. 2. Show devices operating cooler than 50°C. |
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article