- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL - MINUS Operator
MySQL MINUS Operator
The MySQL MINUS operator is one of the set operators which is used to fetch unique records from one table that do not exist in another table. In other words, the MINUS operator compares two tables and returns the unique rows from the first table that do not exist in the second table.
Let's consider the following diagram to understand the MINUS operation:
As we can see in the above diagram, the MINUS operator returned the table containing 1 and 3 values as output because they are the distinct values in table1 which do not exist in table2.
MySQL does not support the MINUS operator, we can use the LEFT JOIN instead of the MINUS operator.
Syntax
Following is the basic syntax of the MINUS operator −
SELECT column_lists FROM table_name WHERE (condition) MINUS SELECT column_lists FROM table_name WHERE (condition);
Unfortunately MySQL does not support the MINUS operator. However we can use the MySQL JOIN clause instead of MINUS operator.
Following is the syntax for JOIN clause to perform MINUS operation −
SELECT column_list FROM table_name1 LEFT JOIN table_name2 ON join_predecate WHERE table_name2.column_name IS NULL;
Example
Firstly, let us create a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Here, we are inserting some records into the CUSTOMERS table using the INSERT statement −
INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00), (2, 'Khilan', 25, 'Delhi', 1500.00), (3, 'Kaushik', 23, 'Kota', 2000.00), (4, 'Chaitali', 25, 'Mumbai', 6500.00), (5, 'Hardik', 27, 'Bhopal', 8500.00), (6, 'Komal', 22, 'Hyderabad', 4500.00), (7, 'Muffy', 24, 'Indore', 10000.00);
The table will be created as −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | 2000.00 |
2 | Khilan | 25 | Delhi | 1500.00 |
3 | Kaushik | 23 | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | 6500.00 |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | 22 | Hyderabad | 4500.00 |
7 | Muffy | 24 | Indore | 10000.00 |
Now, let us create another table named ORDERS using the below query −
CREATE TABLE ORDERS ( OID INT NOT NULL, DATE VARCHAR (20) NOT NULL, ID INT NOT NULL, AMOUNT DECIMAL (18, 2) );
Here, we are inserting some records into the ORDERS table using the INSERT INTO statement −
INSERT INTO ORDERS VALUES (102, '2009-10-08 00:00:00', 3, 3000.00), (100, '2009-10-08 00:00:00', 3, 1500.00), (101, '2009-11-20 00:00:00', 2, 1560.00), (103, '2008-05-20 00:00:00', 4, 2060.00);
The table is created as follows −
OID | DATE | ID | AMOUNT |
---|---|---|---|
102 | 2009-10-08 00:00:00 | 3 | 3000.00 |
100 | 2009-10-08 00:00:00 | 3 | 1500.00 |
101 | 2009-11-20 00:00:00 | 2 | 1560.00 |
103 | 2008-05-20 00:00:00 | 4 | 2060.00 |
As MySQL does not support the MINUS operator instead we can use the JOIN to perform this operation −
The following query selects all the customers who do not have any orders by joining two tables −
SELECT ID, NAME, AGE FROM CUSTOMERS LEFT JOIN ORDERS USING (ID) WHERE ORDERS.ID IS NULL;
Output
The following are the customers who do not have any orders:
ID | NAME | AGE |
---|---|---|
1 | Ramesh | 32 |
5 | Hardik | 27 |
6 | Komal | 22 |
7 | Muffy | 24 |
Minus Operator Using Client Program
In addition to executing the Minus Operator in MySQL table using an SQL query, we can also perform the another operation on a table using a client program.
Syntax
Following are the syntaxes of the Minus Operator in MySQL table in various programming languages −
To perform the Minus Operator in MySQL through a PHP program, we need to execute an SQL statement with JOIN clause using the query() function provided by mysqli connector.
$sql = "SELECT column_lists FROM table_name WHERE (condition) LEFT JOIN SELECT column_lists FROM table_name WHERE (condition)"; $mysqli->query($sql);
To perform the Minus Operator in MySQL through a JavaScript program, we need to execute an SQL statement with JOIN clause using the query() function provided by mysql2 connector.
sql = "SELECT column_list FROM table_name1 LEFT JOIN table_name2 ON join_predecate WHERE table_name2.column_name IS NULL"; con.query(sql);
To perform the Minus Operator in MySQL through a Java program, we need to execute an SQL statement with JOIN clause using the executeQuery() function provided by JDBC type 4 driver.
String sql = "SELECT column_list FROM table_name1 LEFT JOIN table_name2 ON join_predecate WHERE table_name2.column_name IS NULL"; statement.executeQuery(sql);
To perform the Minus Operator in MySQL through a Python program, we need to execute an SQL statement with JOIN clause using the execute() function provided by MySQL Connector/Python.
query = "SELECT column_list FROM table_name1 LEFT JOIN table_name2 ON join_predecate WHERE table_name2.column_name IS NULL" cursorObj.execute(query);
Example
Following are the implementations of this operation in various programming languages −
$dbhost = 'localhost'; $dbuser = 'root'; $dbpass = 'password'; $dbname = 'TUTORIALS'; $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname); if($mysqli->connect_errno ) { printf("Connect failed: %s
", $mysqli->connect_error); exit(); } //printf('Connected successfully.
'); $sql = "SELECT * FROM CUSTOMERS LEFT JOIN ORDERS USING (CUST_ID) WHERE ORDERS.CUST_ID IS NULL"; $result = $mysqli->query($sql); if ($result->num_rows > 0) { printf("Table records: \n"); while($row = $result->fetch_assoc()) { printf("CUST_ID %d, NAME %s, ADDRESS %s, SALARY %f, OID %d, DATE %s, ADDRESS %s, AMOUNT %f", $row["CUST_ID"], $row["NAME"], $row["ADDRESS"], $row["SALARY"], $row["OID"], $row["DATE"], $row["ADDRESS"], $row["AMOUNT"],); printf("\n"); } } else { printf('No record found.
'); } mysqli_free_result($result); $mysqli->close();
Output
The output obtained is as follows −
Table records: CUST_ID 1, NAME Ramesh, ADDRESS , SALARY 2000.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000 CUST_ID 5, NAME Hardik, ADDRESS , SALARY 8500.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000 CUST_ID 6, NAME Komal, ADDRESS , SALARY 4500.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000 CUST_ID 7, NAME Muffy, ADDRESS , SALARY 10000.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
var mysql = require('mysql2'); var con = mysql.createConnection({ host: "localhost", user: "root", password: "Nr5a0204@123" }); //Connecting to MySQL con.connect(function (err) { if (err) throw err; console.log("Connected!"); console.log("--------------------------"); //Creating a Database sql = "create database TUTORIALS" con.query(sql); //Select database sql = "USE TUTORIALS" con.query(sql); //Creating CUSTOMERS table sql = "CREATE TABLE CUSTOMERS (CUST_ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,AGE INT NOT NULL,ADDRESS CHAR(25) NOT NULL,SALARY DECIMAL(18, 2),PRIMARY KEY(CUST_ID));" con.query(sql); //Inserting Records sql = "INSERT INTO CUSTOMERS (CUST_ID,NAME,AGE,ADDRESS,SALARY) VALUES (1,'Ramesh', 32, 'Ahmedabad', 2000.00 ),(2,'Khilan', 25, 'Delhi', 1500.00 ),(3,'kaushik', 23, 'Kota', 2000.00 ),(4,'Chaitali', 25, 'Mumbai', 6500.00 ),(5,'Hardik', 27, 'Bhopal', 8500.00 ),(6,'Komal', 22, 'MP', 4500.00 ),(7,'Muffy', 24, 'Indore', 10000.00 );" con.query(sql); //Creating ORDERS table sql = "CREATE TABLE ORDERS (OID INT NOT NULL,DATE VARCHAR(20) NOT NULL,CUST_ID INT NOT NULL,ADDRESS CHAR(25),AMOUNT INT NOT NULL);" con.query(sql); //Inserting Records sql = "INSERT INTO ORDERS(OID, DATE, CUST_ID, AMOUNT) VALUES(102, '2009-10-08 00:00:00', 3, 3000),(100, '2009-10-08 00:00:00', 3, 1500),(101, '2009-11-20 00:00:00' , 2, 1560),(103, '2008-05-20 00:00:00', 4, 2060);" con.query(sql); //Using MINUS Operator sql = "SELECT * FROM CUSTOMERS LEFT JOIN ORDERS USING (CUST_ID) WHERE ORDERS.CUST_ID IS NULL;" con.query(sql, function(err, result){ if (err) throw err console.log(result) }); });
Output
The output produced is as follows −
Connected! -------------------------- [ { CUST_ID: 1, NAME: 'Ramesh', AGE: 32, ADDRESS: null, SALARY: '2000.00', OID: null, DATE: null, AMOUNT: null }, { CUST_ID: 5, NAME: 'Hardik', AGE: 27, ADDRESS: null, SALARY: '8500.00', OID: null, DATE: null, AMOUNT: null }, { CUST_ID: 6, NAME: 'Komal', AGE: 22, ADDRESS: null, SALARY: '4500.00', OID: null, DATE: null, AMOUNT: null }, { CUST_ID: 7, NAME: 'Muffy', AGE: 24, ADDRESS: null, SALARY: '10000.00', OID: null, DATE: null, AMOUNT: null } ]
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class MinusOperator { public static void main(String[] args) { String url = "jdbc:mysql://localhost:3306/TUTORIALS"; String user = "root"; String password = "password"; ResultSet rs; try { Class.forName("com.mysql.cj.jdbc.Driver"); Connection con = DriverManager.getConnection(url, user, password); Statement st = con.createStatement(); //System.out.println("Database connected successfully...!"); String sql = "SELECT * FROM CUSTOMERS LEFT JOIN ORDERS USING (CUST_ID) WHERE ORDERS.CUST_ID IS NULL"; rs = st.executeQuery(sql); System.out.println("Table records: "); while(rs.next()) { String id = rs.getString("CUST_ID"); String name = rs.getString("NAME"); String age = rs.getString("AGE"); String address = rs.getString("ADDRESS"); String salary = rs.getString("SALARY"); String oid = rs.getString("OID"); String date = rs.getString("DATE"); String address1 = rs.getString("ADDRESS"); String amount = rs.getString("AMOUNT"); System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address + ", Salary: " + salary + ", Oid: " + oid + ", Date: " + date + ", Address: " + address1 + ", Amount: " + amount); } }catch(Exception e) { e.printStackTrace(); } } }
Output
The output obtained is as shown below −
Table records: Id: 1, Name: Ramesh, Age: 32, Address: Ahmedabad, Salary: 2000.00, Oid: null, Date: null, Address: Ahmedabad, Amount: null Id: 5, Name: Hardik, Age: 27, Address: Bhopal, Salary: 8500.00, Oid: null, Date: null, Address: Bhopal, Amount: null Id: 6, Name: Komal, Age: 22, Address: MP, Salary: 4500.00, Oid: null, Date: null, Address: MP, Amount: null Id: 7, Name: Muffy, Age: 24, Address: Indore, Salary: 10000.00, Oid: null, Date: null, Address: Indore, Amount: null
import mysql.connector #establishing the connection connection = mysql.connector.connect( host='localhost', user='root', password='password', database='tut' ) cursorObj = connection.cursor() query = f""" SELECT * FROM CUSTOMERS LEFT JOIN ORDERS USING (CUST_ID) WHERE ORDERS.CUST_ID IS NULL; """ cursorObj.execute(query) # Fetching all the rows that meet the criteria filtered_rows = cursorObj.fetchall() for row in filtered_rows: print(row) cursorObj.close() connection.close()
Output
Following is the output of the above code −
Table records: CUST_ID 1, NAME Ramesh, ADDRESS , SALARY 2000.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000 CUST_ID 5, NAME Hardik, ADDRESS , SALARY 8500.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000 CUST_ID 6, NAME Komal, ADDRESS , SALARY 4500.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000 CUST_ID 7, NAME Muffy, ADDRESS , SALARY 10000.000000, OID 0, DATE , ADDRESS , AMOUNT 0.000000
To Continue Learning Please Login
Login with Google