# SQL INTERSECT OPERATOR

# SQL INTERSECT Operator

### Introduction

SQL (Structured Query Language) is a powerful tool for managing and manipulating relational databases. One of the lesser-known but highly useful operators in SQL is the `INTERSECT`

operator. This article dives deep into the INTERSECT operator, providing a detailed explanation, its differences from other operators, and practical examples.

### What is the SQL INTERSECT Operator?

The `INTERSECT`

operator returns rows that are common between two or more result sets. Think of it as a filter that only lets through records that appear in all of the specified queries.

### Syntax:

```
SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;
```

### INTERSECT vs INNER JOIN

Though they can sometimes provide similar results, `INTERSECT`

and `INNER JOIN`

are fundamentally different.

Feature |
INTERSECT |
INNER JOIN |
---|---|---|

Purpose |
Finds common rows between datasets. | Combines rows based on a condition. |

Column Requirement |
Columns must be of the same data type. | Columns can be different. |

Result |
A single set of columns with common data. | Multiple columns from both tables. |

Duplication |
Automatically removes duplicates from the result. | Can produce duplicate rows. |

### Detailed Examples with Results

### Finding Common Rows in the Same Table

Consider a sample table named `Students`

:

ID |
Name |
Age |
---|---|---|

1 | Alice | 20 |

2 | Bob | 22 |

3 | Carol | 22 |

4 | Dave | 23 |

To find students with the same age:

```
SELECT Age FROM Students WHERE Age = 22
INTERSECT
SELECT Age FROM Students WHERE Age = 23;
```

**Result**:

Age |
---|

22 |

**Explanation**:

Only the age `22`

is common between the two queries.

### INTERSECT with BETWEEN Operator

Using the same `Students`

table:

```
SELECT Age FROM Students WHERE Age BETWEEN 20 AND 22
INTERSECT
SELECT Age FROM Students WHERE Age BETWEEN 21 AND 23;
```

**Result**:

Age |
---|

22 |

**Explanation**:

The age `22`

falls within both specified age ranges.

### INTERSECT with IN Operator

```
SELECT Age FROM Students WHERE Age IN (20, 22)
INTERSECT
SELECT Age FROM Students WHERE Age IN (22, 23);
```

**Result**:

Age |
---|

22 |

**Explanation**:

Once again, only the age `22`

is common in both queries.

### INTERSECT with LIKE Operator

```
SELECT Name FROM Students WHERE Name LIKE 'A%'
INTERSECT
SELECT Name FROM Students WHERE Name LIKE 'Al%';
```

**Result**:

Name |
---|

Alice |

**Explanation**:

The name `Alice`

matches both `LIKE`

patterns.

### Intersect with WHERE Clause

```
SELECT Name FROM Students WHERE Age > 20
INTERSECT
SELECT Name FROM Students WHERE Age < 23;
```

**Result**:

Name |
---|

Bob |

Carol |

**Explanation**:

Both `Bob`

and `Carol`

fit the age criteria defined in both queries.

### SQL Intersect with 3 or More Tables

Suppose we have an additional table `Teachers`

:

ID |
Name |
Age |
---|---|---|

1 | Evan | 22 |

2 | Felicia | 25 |

3 | Gary | 22 |

4 | Helen | 26 |

And another table `Staff`

:

ID |
Name |
Age |
---|---|---|

1 | Ian | 22 |

2 | Jane | 28 |

3 | Kyle | 27 |

4 | Laura | 22 |

Now, to find the common ages among these three tables:

```
SELECT Age FROM Students
INTERSECT
SELECT Age FROM Teachers
INTERSECT
SELECT Age FROM Staff;
```

**Result**:

Age |
---|

22 |

**Explanation**:

The age `22`

is common across all three tables.

### SQL Intersect With Multiple Expressions

We’ll fetch both name and age:

```
SELECT Name, Age FROM Students
INTERSECT
SELECT Name, Age FROM Teachers;
```

**Result**:

Name |
Age |
---|---|

**Explanation**:

There are no common name and age pairs between the two tables.

### SQL Intersect Using ORDER BY Clause

```
(SELECT Name FROM Students)
INTERSECT
(SELECT Name FROM Teachers)
ORDER BY Name;
```

**Result**:

Name |
---|

**Explanation**:

No common names exist between the two tables.

### Common Mistakes and Pitfalls:

**Column Misalignment**: Ensure that the order and data type of columns in both`SELECT`

statements match.**Over-reliance on INTERSECT**: Sometimes, a well-constructed`JOIN`

or`WHERE EXISTS`

might be more efficient than using`INTERSECT`

.

### FAQs:

**How is**`INTERSECT`

different from`UNION`

?`INTERSECT`

returns only the common rows between result sets.`UNION`

combines the result sets and returns all distinct rows.

**How does**`INTERSECT`

handle NULL values?- In the context of
`INTERSECT`

, two`NULL`

values are considered equal.

- In the context of

### Practice Questions:

- Retrieve common names from
`Students`

,`Teachers`

, and`Staff`

tables. - From the
`Students`

table, find students whose names start with`A`

and`B`

, and intersect those results. - Using a hypothetical
`Products`

table, find products that have a price range intersecting between $10-$50 and $40-$80. - From the
`Students`

table, intersect results of students aged 22 with those whose names end with an`e`

.

### Conclusion

In conclusion, the `INTERSECT`

operator is an incredibly useful tool to retrieve common data between result sets. It’s essential to understand its functionality and know when to use it for effective database querying.

**Happy Querying !!**