SQL OPERATORS

Introduction

SQL operators help in filtering, combining, and performing logical comparisons on data. They can be categorized as:

• Comparison Operators (=, !=, <, >, BETWEEN)
• Logical Operators (AND, OR, NOT, EXISTS)
• Pattern Matching Operators (LIKE, IN, IS NULL)
• Set Operators (UNION, INTERSECT, EXCEPT)
• Conditional Operators (CASE)

---

1. AND Operator

Filters data where both conditions must be true.

Example: Retrieve movies released after 2010 AND with a rating above 8.0.

SELECT Title, Year, Rating 
FROM movies 
WHERE Year > 2010 AND Rating > 8.0;

---

2. OR Operator

Filters data where at least one condition is true.

Example: Retrieve movies directed by "Christopher Nolan" OR have a rating above 9.0.

SELECT Title, Director, Rating 
FROM movies 
WHERE Director = 'Christopher Nolan' OR Rating > 9.0;

---

3. Logical Operators (AND, OR, NOT)

• AND → Both conditions must be true.
• OR → At least one condition must be true.
• NOT → Negates a condition.

Example: Retrieve movies that are not in the "Horror" genre.

SELECT Title, Genre 
FROM movies 
WHERE Genre NOT LIKE '%Horror%';

---

4. LIKE Operator

Used for pattern matching in text fields.

• % → Matches multiple characters.
• _ → Matches a single character.

Example: Retrieve movies whose title starts with 'The'.

SELECT Title 
FROM movies 
WHERE Title LIKE 'The%';

Example: Retrieve movies whose title contains 'War'.

SELECT Title 
FROM movies 
WHERE Title LIKE '%War%';

---

5. IN Operator

Filters records that match any value in a given list.

Example: Retrieve movies in the Action, Comedy, or Drama genres.

SELECT Title, Genre 
FROM movies 
WHERE Genre IN ('Action', 'Comedy', 'Drama');

---

6. NOT Operator

Negates a condition.

Example: Retrieve movies not directed by "Steven Spielberg".

SELECT Title, Director 
FROM movies 
WHERE Director NOT IN ('Steven Spielberg');

---

7. NOT EQUAL Operator (!= or <>)

Filters records where values are not equal.

Example: Retrieve movies not released in 2000.

SELECT Title, Year 
FROM movies 
WHERE Year <> 2000;

---

8. IS NULL Operator

Finds records where a column has a NULL value.

Example: Retrieve movies where the revenue information is missing.

SELECT Title, Revenue 
FROM movies 
WHERE Revenue IS NULL;

---

9. UNION Operator

Combines results from two queries, removing duplicates.

Example: Retrieve unique movie titles directed by either "Quentin Tarantino" or "Martin Scorsese".

SELECT Title FROM movies WHERE Director = 'Quentin Tarantino'
UNION
SELECT Title FROM movies WHERE Director = 'Martin Scorsese';

---

10. UNION ALL Operator

Similar to UNION, but keeps duplicates.

Example: Retrieve all movie titles directed by Tarantino or Scorsese, including duplicates.

SELECT Title FROM movies WHERE Director = 'Quentin Tarantino'
UNION ALL
SELECT Title FROM movies WHERE Director = 'Martin Scorsese';

---

11. EXCEPT Operator

Returns records from the first query that are not present in the second query.

Example: Retrieve movies directed by "Christopher Nolan" but not starring "Christian Bale".

SELECT Title FROM movies WHERE Director = 'Christopher Nolan'
EXCEPT
SELECT Title FROM movies WHERE Actor LIKE '%Christian Bale%';

---

12. BETWEEN Operator

Filters data within a range.

Example: Retrieve movies with a rating between 7.0 and 9.0.

SELECT Title, Rating 
FROM movies 
WHERE Rating BETWEEN 7.0 AND 9.0;

---

13. ALL and ANY Operators

• ALL → Compares values to all returned values.
• ANY → Compares values to at least one returned value.

Example: Retrieve movies that have a higher rating than all movies from 2000.

SELECT Title, Rating 
FROM movies 
WHERE Rating > ALL (SELECT Rating FROM movies WHERE Year = 2000);

Example: Retrieve movies that have a higher rating than at least one movie from 2000.

SELECT Title, Rating 
FROM movies 
WHERE Rating > ANY (SELECT Rating FROM movies WHERE Year = 2000);

---

14. INTERSECT Operator

Returns common records in both queries.

Example: Retrieve movies that both Quentin Tarantino and Martin Scorsese directed.

SELECT Title FROM movies WHERE Director = 'Quentin Tarantino'
INTERSECT
SELECT Title FROM movies WHERE Director = 'Martin Scorsese';

---

15. EXISTS Operator

Checks if a subquery returns any records.

Example: Retrieve all movies with at least one vote in the votes column.

SELECT Title, Votes 
FROM movies 
WHERE EXISTS (SELECT 1 FROM movies WHERE Votes > 0);

---

16. CASE Operator

Used for conditional logic in queries.

Example: Categorize movies based on rating.

SELECT Title, Rating, 
    CASE 
        WHEN Rating >= 8.0 THEN 'Excellent'
        WHEN Rating BETWEEN 6.0 AND 7.9 THEN 'Good'
        ELSE 'Average'
    END AS Rating_Category
FROM movies;

---