Cara menggunakan mysql intersect

Question

Apa itu Intersect?

Query Intersect adalah membandingkan hasil dua query antara query A dan query B dan hanya menampilkan record yang sama hasil dari kedua query tersebut.

Table of Contents Show

Apa itu Intersect?
Sintak Dasar
About Unknown
Introduction to the INTERSECT operator
Emulating INTERSECT in MySQL
Setting up sample tables
1) Emulate INTERSECT using DISTINCT and INNER JOIN clause
2) Emulate INTERSECT using IN and subquery

Query Intersect digambarkan sebagai berikut:

Sintak Dasar

Di bawah ini adalah sintak dasar SQL Query menggunakan Intersect clause pada Oracle SQL:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
INTERSECT
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Contoh

Asumsikan Anda telah mempunyai dua tabel sebagai contoh pada tutorial kali ini, yaitu tabel Customer dan tabel Orders dengan data-data sebagai berikut:

Tabel Customer

SQL> SELECT * FROM customer;

CUSTOMER_ID CUSTOMER_NAME     CUSTOMER_ADDRESS
----------- ----------------- -----------------
CS001       Nursalim          Bandung
CS002       Nani Indriyani    Jakarta
CS003       Naura Krasiva     Surabaya
CS004       Ahmad Fatoni      Semarang
CS005       Sri Rahayu        Yogyakarta

Tabel Orders

SQL> SELECT * FROM orders;

ORDER_ID ORDER_DATE CUSTOMER_ID QTY   AMOUNT
-------- ---------- ----------- ----- -------
00001    01-04-2015 CS001       2     10000
00002    08-04-2015 CS001       3     7000
00003    14-04-2015 CS004       1     15000

Di bawah ini adalah contoh Query Intersect menggunakan kedua tabel diatas:

SQL> SELECT customer.customer_id, customer.customer_name, orders.order_id, orders.order_date, orders.qty, orders.amount
  2  FROM customer
  3  LEFT JOIN orders
  4  ON customer.customer_id = orders.customer_id
  5  INTERSECT
  6  SELECT customer.customer_id, customer.customer_name, orders.order_id, orders.order_date, orders.qty, orders.amount
  7  FROM customer
  8  RIGHT JOIN orders
  9  ON customer.customer_id = orders.customer_id;

CUSTOMER_ID CUSTOMER_NAME     ORDER_ID ORDER_DATE QTY   AMOUNT
----------- ----------------- -------- ---------- ----- --------
CS001       Nursalim          00001    01-04-2015     2    10000
CS001       Nursalim          00002    08-04-2015     3     7000
CS004       Ahmad Fatoni      00003    14-04-2015     1    15000

About Unknown

This is a short description in the author block about the author. You edit it by entering text in the "Biographical Info" field in the user admin panel.

Summary: in this tutorial, we will introduce you to the INTERSECT operator and show you how to emulate the MySQL INTERSECT operator.

Note that MySQL does not support the INTERSECT operator. This tutorial introduces you to how to emulate the INTERSECT operator in MySQL using join clauses.

Introduction to the INTERSECT operator

The INTERSECT operator is a set operator that returns only distinct rows of two queries or more queries.

The following illustrates the syntax of the INTERSECT operator.

(SELECT column_list 
FROM table_1)
INTERSECT
(SELECT column_list
FROM table_2);
Code language: SQL (Structured Query Language) (sql)

The INTERSECT operator compares the result sets of two queries and returns the distinct rows that are output by both queries.

To use the INTERSECT operator for two queries, you follow these rules:

The order and the number of columns in the select list of the queries must be the same.
The data types of the corresponding columns must be compatible.

The following diagram illustrates the INTERSECT operator.

The left query produces a result set of (1,2,3).

The right query returns a result set of (2,3,4).

The INTERSECT operator returns the distinct rows of both result sets which include (2,3).

Unlike the UNION operator, the INTERSECT operator returns the intersection between two circles.

Note that the SQL standard has three set operators that include UNION, INTERSECT, and MINUS.

Emulating INTERSECT in MySQL

Unfortunately, MySQL does not support the INTERSECT operator. However, you can emulate the INTERSECT operator.

Setting up sample tables

The following statements create tables t1 and t2, and then insert data into both tables.

CREATE TABLE t1 (
    id INT PRIMARY KEY
);

CREATE TABLE t2 LIKE t1;

INSERT INTO t1(id) VALUES(1),(2),(3);

INSERT INTO t2(id) VALUES(2),(3),(4);
Code language: SQL (Structured Query Language) (sql)

The following query returns rows from the t1 table.

SELECT id FROM t1;
Code language: SQL (Structured Query Language) (sql)

id
----
1
2
3

The following query returns the rows from the t2 table:

SELECT id
FROM t2;
Code language: SQL (Structured Query Language) (sql)

id
---
2
3
4

1) Emulate INTERSECT using DISTINCT and INNER JOIN clause

The following statement uses DISTINCT operator and INNER JOIN clause to return the distinct rows in both tables:

SELECT DISTINCT 
   id 
FROM t1
   INNER JOIN t2 USING(id);
Code language: SQL (Structured Query Language) (sql)

id
----
2
3

How it works.

The INNER JOIN clause returns rows from both left and right tables.
The DISTINCT operator removes the duplicate rows.

2) Emulate INTERSECT using IN and subquery

The following statement uses the IN operator and a subquery to return the intersection of the two result sets.

SELECT DISTINCT id
FROM t1
WHERE id IN (SELECT id FROM t2);
Code language: SQL (Structured Query Language) (sql)

id
----
2
3

How it works.

The subquery returns the first result set.
The outer query uses the IN operator to select only values that exist in the first result set. The DISTINCT operator ensures that only distinct values are selected.

In this tutorial, you have learned a couple of ways to simulate the INTERSECT operator in MySQL.

Was this tutorial helpful?

kode mysql MINUS pada sql