PostgreSQL Tutorial

  • Home
  • Stored Procedures
  • Triggers
  • Views
  • Interfaces
    • PostgreSQL PHP
    • PostgreSQL Python
    • PostgreSQL JDBC
  • Functions
Home / PostgreSQL Tutorial / PostgreSQL INTERSECT Operator

PostgreSQL INTERSECT Operator

Summary: in this tutorial, you will learn how to use the PostgreSQL INTERSECT operators to combine two or more SELECT statements to form a single result set.

Introduction to PostgreSQL INTERSECT operator

Like the UNION and EXCEPT operators, the PostgreSQL INTERSECT operator combines the result sets of two or more SELECT statements into a single result set. The INTERSECT operator returns all rows in the both result sets.

The following illustration shows the final result set produced by the INTERSECT operator. The final result set is represented by the yellow area where the circle A intersects with the circle B.

PostgreSQL INTERSECT Operator

The following illustrates the syntax of the INTERSECT operator:

1
2
3
4
5
6
7
8
9
SELECT
column_list
FROM
A
INTERSECT
SELECT
column_list
FROM
B;

To use the INTERSECT operator, the columns that appear in the SELECT statements must follow the rules below:

  1. The number of columns and their order in the SELECT clauses must the be the same.
  2. The data types of the columns must be compatible.

PostgreSQL INTERSECT operator example

Let’s create some tables for the demonstration.

The following CREATE TABLE statement create three tables: employees , keys, and  hipos.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE employees (
employee_id serial PRIMARY KEY,
employee_name VARCHAR (255) NOT NULL
);
 
CREATE TABLE keys (
employee_id INT PRIMARY KEY,
effective_date DATE NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);
 
CREATE TABLE hipos (
employee_id INT PRIMARY KEY,
effective_date DATE NOT NULL,
FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);

The employees table stores the employee master data. The keys table stores the key employees and the  hipos table stores the employees who are high potential and high impact.

The following INSERT statements insert some sample data into the employees, keys, and  hipos tables:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
INSERT INTO employees (employee_name)
VALUES
('Joyce Edwards'),
('Diane Collins'),
('Alice Stewart'),
('Julie Sanchez'),
('Heather Morris'),
('Teresa Rogers'),
('Doris Reed'),
('Gloria Cook'),
('Evelyn Morgan'),
('Jean Bell');
 
INSERT INTO keys
VALUES
(1, '2000-02-01'),
(2, '2001-06-01'),
(5, '2002-01-01'),
(7, '2005-06-01');
 
INSERT INTO hipos
VALUES
(9, '2000-01-01'),
(2, '2002-06-01'),
(5, '2006-06-01'),
(10, '2005-06-01');

The following statement returns the key employees from the keys table.

1
2
3
4
SELECT
employee_id
FROM
keys;

1
2
3
4
5
6
7
employee_id
-------------
           1
           2
           5
           7
(4 rows)

The following statement returns a list of employee_id from the  hipos table.

1
2
3
SELECT
   employee_id
FROM hipos;

1
2
3
4
5
6
7
employee_id
-------------
           9
           2
           5
          10
(4 rows)

To get the employees who are both key, and high potential and high impact.

1
2
3
4
5
6
7
8
9
SELECT
employee_id
FROM
keys
INTERSECT
SELECT
        employee_id
FROM
hipos;

1
2
3
4
5
employee_id
-------------
           5
           2
(2 rows)

The result set shows that the employee id 2 and 5 are the are key, high potential, and high impact employees.

To sort the result set returned by the INTERSECT operator, you place the ORDER BY clause at the end of the statement, not at the end of each SELECT statement.

See the following query:

1
2
3
4
5
6
7
8
9
10
SELECT
employee_id
FROM
keys
INTERSECT
SELECT
        employee_id
FROM
hipos
ORDER BY employee_id;

1
2
3
4
5
employee_id
-------------
           2
           5
(2 rows)

In this tutorial, we have shown you how to use the PostgreSQL INTERSECT operator to combine multiple result sets and return the rows that appear in both result sets.

Related Tutorials

  • PostgreSQL UNION
  • PostgreSQL EXCEPT Operator
Previous Tutorial: PostgreSQL UNION
Next Tutorial: PostgreSQL EXCEPT Operator

PostgreSQL Quick Start

  • What is PostgreSQL?
  • Install PostgreSQL
  • Connect to Database
  • Download PostgreSQL Sample Database
  • Load Sample Database
  • Explore Server and Database Objects

PostgreSQL Fundamentals

  • PostgreSQL Select
  • PostgreSQL Order By
  • PostgreSQL Select Distinct
  • PostgreSQL Where
  • PostgreSQL LIMIT
  • PostgreSQL IN
  • PostgreSQL Between
  • PostgreSQL Like
  • PostgreSQL Inner Join
  • PostgreSQL Left Join
  • PostgreSQL Full Outer Join
  • PostgreSQL Cross Join
  • PostgreSQL Natural Join
  • PostgreSQL Group By
  • PostgreSQL Having
  • PostgreSQL Union
  • PostgreSQL Intersect
  • PostgreSQL Except
  • PostgreSQL Subquery
  • PostgreSQL Insert
  • PostgreSQL Update
  • PostgreSQL Delete
  • PostgreSQL Data Types
  • PostgreSQL Create Table
  • PostgreSQL Alter Table
  • PostgreSQL Drop Table
  • PostgreSQL Truncate Table
  • PostgreSQL CHECK Constraint
  • PostgreSQL Not-Null Constraint
  • PostgreSQL Foreign Key
  • PostgreSQL Primary Key
  • PostgreSQL UNIQUE Constraint

About PostgreSQL Tutorial

PostgreSQLTutorial.com is a website dedicated to developers and database administrators who are working on PostgreSQL database management system.

We constantly publish useful PostgreSQL tutorials to keep you up-to-date with the latest PostgreSQL features and technologies. All PostgreSQL tutorials are simple, easy-to-follow and practical.

Recent PostgreSQL Tutorials

  • How To Change The Password of a PostgreSQL User
  • PostgreSQL AGE Function
  • PostgreSQL DATE_PART Function
  • PostgreSQL List Users
  • PostgreSQL NOW Function
  • PostgreSQL DATE_TRUNC Function
  • PostgreSQL TO_DATE Function: Convert String to Date
  • A Look at PostgreSQL User-defined Data Types
  • PostgreSQL Copy Database Made Easy
  • How to Get Table, Database, Indexes, Tablespace, and Value Size in PostgreSQL

More Tutorials

  • PostgreSQL Cheat Sheet
  • PostgreSQL Administration
  • PostgreSQL PHP
  • PostgreSQL Python
  • PostgreSQL JDBC
  • PostgreSQL Resources

Site Info

  • Home
  • About Us
  • Contact Us
  • Privacy Policy

Copyright © 2017 by PostgreSQL Tutorial Website. All Rights Reserved.