What is INTERSECT OPERATOR in PostgreSQL

The INTERSECT operator in PostgreSQL is used to combine the results of two SELECT statements and returns only the rows that are common to both result sets. Here's an example and coding demonstration:

Suppose we have two tables named "table1" and "table2" with the following data:

table1:

id | name

---|------

1  | Alice

2  | Bob

3  | Charlie

4  | David

table2:

id | name

---|------

2  | Bob

3  | Charlie

5  | Emma

Now, let's use the INTERSECT operator to find the common names between these two tables:

-- Create table1

CREATE TABLE table1 (

    id INT,

    name VARCHAR(50)

);

-- Insert data into table1

INSERT INTO table1 (id, name)

VALUES 

    (1, 'Alice'),

    (2, 'Bob'),

    (3, 'Charlie'),

    (4, 'David');

-- Create table2

CREATE TABLE table2 (

    id INT,

    name VARCHAR(50)

);

-- Insert data into table2

INSERT INTO table2 (id, name)

VALUES 

    (2, 'Bob'),

    (3, 'Charlie'),

    (5, 'Emma');

-- Use INTERSECT operator to find common names

SELECT name FROM table1

INTERSECT

SELECT name FROM table2;

Output:

name   

--------

Bob

Charlie

In this example, the INTERSECT operator returns the common names between "table1" and "table2", which are 'Bob' and 'Charlie'.

Here are five frequently asked questions (FAQs) about the INTERSECT operator in PostgreSQL:

1. What does the INTERSECT operator do in PostgreSQL?

   - Answer: The INTERSECT operator in PostgreSQL is used to combine the results of two SELECT statements and return only the rows that are common to both result sets.

2. How does the INTERSECT operator differ from the JOIN operation in PostgreSQL?

   - Answer: While the INTERSECT operator returns only the common rows between two result sets, a JOIN operation combines rows from two or more tables based on a related column between them.

3. Can the INTERSECT operator be used with more than two SELECT statements in PostgreSQL?

   - Answer: Yes, the INTERSECT operator can be used with multiple SELECT statements to find the common rows among all result sets.

4. Does the INTERSECT operator consider duplicate rows in PostgreSQL?

   - Answer: No, the INTERSECT operator in PostgreSQL eliminates duplicate rows from the final result set, ensuring that each row is unique.

5. Is the INTERSECT operator in PostgreSQL case-sensitive?

   - Answer: Yes, the INTERSECT operator in PostgreSQL is case-sensitive by default, meaning it considers case differences when comparing rows from different result sets.