SQL INTERSECT Tutorial 🎯

Welcome to our comprehensive guide on the SQL INTERSECT operator! This tutorial is designed for both beginners and intermediate learners. By the end of this lesson, you'll have a solid understanding of how to use INTERSECT in your SQL queries to find the common records between two tables. 📝 Note: This tutorial assumes you're already familiar with the basics of SQL.

What is SQL INTERSECT? 💡

The SQL INTERSECT operator is used to find the common records between the results of two separate SQL queries. It returns only the matching rows and eliminates duplicates.

Understanding the SQL INTERSECT Syntax 💡

The basic syntax for the SQL INTERSECT operator is:

sql

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

In this syntax, replace column1, column2, ... with the names of the columns you want to select, and table1 and table2 with the names of your tables.

Practical Example 🎯

Let's consider two tables, students and courses, with common columns student_id and course_id.

sql

CREATE TABLE students (
    student_id INT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    course_id INT
);

CREATE TABLE courses (
    course_id INT,
    course_name VARCHAR(100),
    enrolled_students INT
);

-- Sample data for students table
INSERT INTO students (student_id, first_name, last_name, course_id)
VALUES (1, 'John', 'Doe', 101), (2, 'Jane', 'Doe', 102), (3, 'Mike', 'Smith', 101), (4, 'Sarah', 'Johnson', 102);

-- Sample data for courses table
INSERT INTO courses (course_id, course_name, enrolled_students)
VALUES (101, 'Database Fundamentals', 3), (102, 'Web Development', 2);

Now, let's find the common students enrolled in courses 101 and 102 using the SQL INTERSECT operator:

sql

SELECT first_name, last_name
FROM students
WHERE course_id = 101
INTERSECT
SELECT first_name, last_name
FROM students
WHERE course_id = 102;

-- Result:
-- John Doe
-- Mike Smith

INTERSECT vs UNION 💡

The SQL UNION operator is similar to INTERSECT, but it combines the results of two or more queries and eliminates duplicates. The key difference between INTERSECT and UNION is that INTERSECT only returns common records, while UNION returns all unique records from all queries.

Quiz 🎯

Advanced Example 🎯

Suppose you have a transactions table with the following columns: transaction_id, customer_id, product_id, and amount. You can find the common products purchased by customers with IDs 1 and 2 using the INTERSECT operator:

sql

SELECT product_id
FROM transactions
WHERE customer_id = 1
INTERSECT
SELECT product_id
FROM transactions
WHERE customer_id = 2;

By the end of this tutorial, you should have a good understanding of how to use the SQL INTERSECT operator effectively in your SQL queries. Happy coding! 💡 Pro Tip: Don't forget to test your queries using the appropriate SQL management system like MySQL, PostgreSQL, or SQL Server.