Khóa học miễn phí MySQL – Using Joins nhận dự án làm có lương

MySQL – Using Joins

Table of content

A Join clause in MySQL is used to combine records from two or more tables in a database. These tables are joined together based on a condition, specified in a WHERE clause.

For example, comparing the equality (=) of values in similar columns of two different tables can be considered as a join-predicate. In addition, several operators can be used to join tables, such as <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT etc.

We can use JOINS in the SELECT, UPDATE and DELETE statements to join the MySQL tables.

Types of Joins

There are various types of Joins provided by SQL which are categorized based on the way data across multiple tables are joined together. They are listed as follows −

Inner Join − An Inner Join retrieves the intersection of two tables. It compares each row of the first table with each row of the second table. If the pairs of these rows satisfy the join-predicate, they are joined together. This is a default join.
Outer Join − An Outer Join retrieves all the records in two tables even if there is no counterpart row of one table in another table, like Inner Join. Outer join is further divided into three subtypes: Left Join, Right Join and Full Join. We will learn about these Joins later in this tutorial.

Example

In this example, we first create a table named CUSTOMERS using the CREATE TABLE query as follows −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(15) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS VARCHAR(25),
   SALARY DECIMAL(10, 2),
   PRIMARY KEY(ID)
);

Let us then insert the following records in the CUSTOMERS table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(3, ''Kaushik'', ''23'', ''Kota'', 2500),
(4, ''Chaitali'', ''26'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(6, ''Komal'', ''22'', ''Hyderabad'', 9000),
(7, ''Muffy'', ''24'', ''Indore'', 5500);

The table is 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

ORDERS Table −

We create another table named ORDERS containing details of orders made by the customers, using the following CREATE TABLE query −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Using the INSERT statement, insert values into the ORDERS table as follows −

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 displayed as follows −

OID	DATE	CUSTOMER_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

Joining Tables −

Now we write an SQL query to join these two tables. This query will select all the customers from table CUSTOMERS and will pick up the corresponding number of orders made by them from the ORDERS.

SELECT a.ID, a.NAME, b.DATE, b.AMOUNT
FROM CUSTOMERS a, ORDERS b
WHERE a.ID = b.CUSTOMER_ID;

Output

The table is displayed as follows −

ID	NAME	DATE	AMOUNT
3	Kaushik	2009-10-08 00:00:00	3000.00
3	Kaushik	2009-10-08 00:00:00	1500.00
2	Khilan	2009-11-20 00:00:00	1560.00
4	Chaitali	2008-05-20 00:00:00	2060.00

Joins Using a Client Program

In addition to Join two or more than two tables using the MySQL query we can also perform the Join operation using a client program.

Syntax

To join two or more than two MySQL tables through a PHP program, we need to perform the JOIN operation using the mysqli function query() as follows −

$sql = ''SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID
$mysqli->query($sql);

To join two or more than two MySQL tables through a JavaScript program, we need to perform the JOIN operation using the query() function of mysql2 library as follows −

sql = ''SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID
con.query(sql);

To join two or more than two MySQL tables through a Java program, we need to perform the JOIN operation using the JDBC function executeQuery() as follows −

String sql = ''SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID
st.executeQuery(sql);

To join two or more than two MySQL tables through a Python program, we need to perform the JOIN operation using the execute() function of the MySQL Connector/Python as follows −

using_join_query = ''SELECT a.ID, a.NAME, b.DATE, b.AMOUNT FROM CUSTOMERS a, ORDERS b WHERE a.ID = b.CUSTOMER_ID''
cursorObj.execute(using_join_query)

Example

Following are the programs −

$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 a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        printf(
            "Id: %s, Author: %s, Count: %d 
",
            $row["tutorial_id"],
            $row["tutorial_author"],
            $row["tutorial_count"]
        );
    }
} else {
    printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Id: 3, Author: Sanjay, Count: 1

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
//   console.log("Connected successfully...!");
//   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);
  sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

Output

The output produced is as follows −

[ { tutorial_id: 1, tutorial_author: ''John Poul'', tutorial_count: 2 } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Join {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Mysql JOIN...!;
         String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
         ResultSet resultSet = statement.executeQuery(sql);
         System.out.println("Table records after join...!");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getString(2)+" "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Output

The output obtained is as shown below −

Connected successfully...!
Table records after join...!
1 John Paul 1
3 Sanjay 1

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
using_join_query = f"""SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUST_ID"""
cursorObj.execute(using_join_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 −

(3, ''Kaushik'', 23, 3000)
(3, ''Kaushik'', 23, 1500)
(2, ''Khilan'', 25, 1560)
(4, ''Chaital'', 25, 2060)

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – UNION vs UNION ALL nhận dự án làm có lương

MySQL – UNION vs UNION ALL

Table of content

UNION and UNION ALL operators in MySQL are used to retrieve the rows from multiple tables and return them as one single table.

However, for these operators to work on these tables, they need to follow the conditions given below −

The tables to be combined must have the same number of columns with the same datatype.
The number of rows need not be the same.

MySQL UNION Operator

UNION operator in MySQL works similar to the union operator in relational algebra. This operator combines information from multiple tables that are union compatible. However, only distinct rows from the tables are added to the result table, as UNION automatically eliminates all the duplicate records.

Syntax

Following is the syntax of UNION operator in MySQL −

SELECT * FROM table1
UNION
SELECT * FROM table2;

MySQL UNION ALL Operator

UNION ALL is also an operator/clause in MySQL, used to combine multiple tables into one. However, this operator also preserves the duplicate rows in the resultant tables.

Syntax

Following is the syntax of UNION ALL operator in MySQL −

SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

MySQL UNION VS MySQL UNION ALL

The only difference between these two operators is that UNION only returns distinct rows while UNION ALL returns all the rows present in both tables. Let”s try to understand this with an example.

Example

First of all we need to create two tables namely with the same number of columns having the same data types. Following query creates a table named “COURSES_PICKED” −

CREATE TABLE COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   COURSE_NAME VARCHAR(30) NOT NULL
);

Following query inserts values into the COURSES_PICKED table −

INSERT INTO COURSES_PICKED VALUES
(1, ''JOHN'', ''ENGLISH''),
(2, ''ROBERT'', ''COMPUTER SCIENCE''),
(3, ''SASHA'', ''COMMUNICATIONS''),
(4, ''JULIAN'', ''MATHEMATICS'');

The contents of the COURSES_PICKED table will be as follows −

STUDENT_ID	STUDENT_NAME	COURSE_NAME
1	JOHN	ENGLISH
2	ROBERT	COMPUTER SCIENCE
3	SASHA	COMMUNICATIONS
4	JULIAN	MATHEMATICS

Now, lets create another table named EXTRA_COURSES_PICKED as shown below −

CREATE TABLE EXTRA_COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   EXTRA_COURSE_NAME VARCHAR(30) NOT NULL
);

Following is the query to insert values into the EXTRA_COURSES_PICKED table −

INSERT INTO EXTRA_COURSES_PICKED VALUES
(1, ''JOHN'', ''PHYSICAL EDUCATION''),
(2, ''ROBERT'', ''GYM''),
(3, ''SASHA'', ''FILM''),
(4, ''JULIAN'', ''MATHEMATICS'');

The contents of the EXTRA_COURSES_PICKED table will be as follows −

STUDENT_ID	STUDENT_NAME	COURSES_PICKED
1	JOHN	PHYSICAL EDUCATION
2	ROBERT	GYM
3	SASHA	COMMUNICATIONS
4	JULIAN	MATHEMATICS

Combining tables using UNION Operator

Now, let us combine both these tables using the UNION query as follows −

SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;

Output

Following is the output obtained −

STUDENT_ID	STUDENT_NAME	COURSE_NAME
1	JOHN	ENGLISH
2	ROBERT	COMPUTER SCIENCE
3	SASHA	COMMUNICATIONS
4	JULIAN	MATHEMATICS
1	JOHN	PHYSICAL EDUCATION
2	ROBERT	GYM

In here, the extra courses and courses picked by “Julian” and “Shasha” are same therefore, the last two records in the COURSES_PICKED and EXTRA_COURSES_PICKED are identical. When we combine both the tables using the UNION operator it excludes the identical records and returns only distinct records.

Combining tables using UNION ALL Operator

Unlike the UNION operator if we combine the above created tables using the UNION ALL operator it reruns all the records in both tables −

SELECT * FROM COURSES_PICKED
UNION ALL
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table is displayed as follows −

STUDENT_ID	STUDENT_NAME	COURSE_NAME
1	JOHN	ENGLISH
2	ROBERT	COMPUTER SCIENCE
3	SASHA	COMMUNICATIONS
4	JULIAN	MATHEMATICS
1	JOHN	PHYSICAL EDUCATION
2	ROBERT	GYM
3	SASHA	COMMUNICATIONS
4	JULIAN	MATHEMATICS

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – UNION Operator nhận dự án làm có lương

MySQL – UNION Operator

Table of content

MySQL UNION Operator

The UNION operator in MySQL combines the data (without duplicate records) from multiple tables.

We can use UNION if we want to select rows one after the other from several tables or several sets of rows from a single table all as a single result set.

To use the UNION operator on multiple tables, all these tables must be union compatible. And they are said to be union compatible if and only if they meet the following criteria −

The same number of columns selected with the same datatype.
These columns must also be in the same order.
They need not have same number of rows.

Once these criterion are met, the UNION operator returns the rows from multiple tables as a resultant table which is void of all duplicate values from these tables.

UNION is available as of MySQL 4.0. This section illustrates how to use it.

Syntax

The basic syntax of UNION operator in MySQL is as follows −

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Example

Let us first create the prospective customers table named PROSPECT using the following CREATE TABLE query −

CREATE TABLE PROSPECT (
   FNAME CHAR(20) NOT NULL,
   LNAME CHAR(20),
   ADDRESS VARCHAR(100) NOT NULL
);

Now, we insert records into this table using INSERT statement below −

INSERT INTO PROSPECT VALUES
(''Peter'', ''Jones'', ''482 Rush St., Apt. 402''),
(''Bernice'', ''Smith'', ''916 Maple Dr.'');

The PROSPECT table is created as −

FNAME	LNAME	ADDRESS
Peter	Jones	482 Rush St., Apt. 402
Bernice	Smith	916 Maple Dr.

ACTIVE Table −

We then create an active customers table named ACTIVE using the following CREATE TABLE query −

CREATE TABLE ACTIVE (
   FNAME CHAR(20) NOT NULL,
   LNAME CHAR(20),
   ADDRESS VARCHAR(100) NOT NULL
);

Using the following INSERT statement, insert records into the ACTIVE table −

INSERT INTO ACTIVE VALUES
(''Grace'', ''Peterson'', ''16055 Seminole Ave.''),
(''Bernice'', ''Smith'', ''916 Maple Dr.''),
(''Walter'', ''Brown'', ''8602 1st St.'');

The ACTIVE table is created as −

FNAME	LNAME	ADDRESS
Grace	Peterson	16055 Seminole Ave.
Bernice	Smith	916 Maple Dr.
Walter	Brown	8602 1st St.

Now, you want to create a single mailing list by merging names and addresses from all the tables. UNION provides a way to do this.

The following query illustrates how to select names and addresses from the tables all at once −

SELECT FNAME, LNAME, ADDRESS FROM PROSPECT
UNION
SELECT FNAME, LNAME, ADDRESS FROM ACTIVE;

Output

Following output is obtained −

FNAME	LNAME	ADDRESS
Peter	Jones	482 Rush St., Apt. 402
Bernice	Smith	916 Maple Dr.
Grace	Peterson	16055 Seminole Ave.
Walter	Brown	8602 1st St.

As you can see, duplicates are avoided in the result-set.

UNION with WHERE clause

We can use the WHERE clause with UNION operator to filter the results of each SELECT statement before combining them.

Syntax

Following is the syntax for using the WHERE clause with UNION operator −

SELECT column1, column2, column3
FROM table1
WHERE column1 = ''value1''
UNION
SELECT column1, column2, column3
FROM table2
WHERE column1 = ''value2

Example

Let us use the same tables from the previous example to retrieve combined records using UNION operator with WHERE clause −

SELECT FNAME, LNAME, ADDRESS FROM PROSPECT WHERE LNAME = ''Jones''
UNION
SELECT FNAME, LNAME, ADDRESS FROM ACTIVE WHERE LNAME = ''Peterson

Output

Following output is obtained −

FNAME	LNAME	ADDRESS
Peter	Jones	482 Rush St., Apt. 402
Grace	Peterson	16055 Seminole Ave.

UNION with ORDER BY clause

When we use UNION with ORDER BY clause, it combines the sorted result sets of all SELECT statements and produces a single sorted result set.

Syntax

Following is the basic syntax to use UNION operator with ORDER BY clause −

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2
ORDER BY column_name;

Example

Let us try to sort the table records in ascending order with respect to values in the ”lname” column of result set, using the following query −

SELECT FNAME, LNAME, ADDRESS FROM PROSPECT
UNION
SELECT FNAME, LNAME, ADDRESS FROM ACTIVE
ORDER BY LNAME;

Output

Following output is obtained −

FNAME	LNAME	ADDRESS
Walter	Brown	8602 1st St.
Peter	Jones	482 Rush St., Apt. 402
Grace	Peterson	16055 Seminole Ave.
Bernice	Smith	916 Maple Dr.

UNION with Aliases

We can use aliases in a MySQL statement of UNION operator to give a table or column a temporary name, which can be useful when working with multiple tables or columns with similar names.

When using UNION with aliases, it”s important to note that the column aliases are determined by the first SELECT statement. Therefore, if you want to use different aliases for the same column in different SELECT statements, you need to use column aliases in all SELECT statements to ensure consistent column names in the final result set.

Syntax

Following is the syntax for using Union with Aliases −

SELECT column1 AS alias1, column2 AS alias2
FROM table1
UNION
SELECT column3 AS alias1, column4 AS alias2
FROM table2;

Example

In this following example, we are trying to combine two tables using aliases to represent the fields in result-set obtained −

SELECT FNAME AS Firstname,
LNAME AS Lastname, ADDRESS AS Address
FROM PROSPECT UNION
SELECT FNAME, LNAME, ADDRESS FROM ACTIVE;

Output

Following output is obtained −

Firstname	Lastname	Address
Peter	Jones	482 Rush St., Apt. 402
Bernice	Smith	916 Maple Dr.
Grace	Peterson	16055 Seminole Ave.
Walter	Brown	8602 1st St.

UNION ALL Operator

If you want to select all records, including duplicates, follow the first UNION keyword with ALL −

SELECT fname, lname, ADDRESS  FROM prospect
UNION ALL
SELECT fname, lname, ADDRESS  FROM active;

Output

Following output is obtained −

FNAME	LNAME	ADDRESS
Peter	Jones	482 Rush St., Apt. 402
Bernice	Smith	916 Maple Dr.
Grace	Peterson	16055 Seminole Ave.
Bernice	Smith	916 Maple Dr.
Walter	Brown	8602 1st St.

UNION Operator Using Client Program

In addition to applying the UNION Operator in MySQL table directly in MySQL server, we can also apply the UNION operation on a MySQL table using a client program.

Syntax

Following are the syntaxes of the UNION Operator in MySQL table in various programming languages −

To combine tables using UNION operator through a PHP program, we need to execute the SQL statement with UNION operator using the mysqli function named query() as follows −

$sql = "SELECT column_name(s) FROM table1 UNION SELECT column_name(s)
FROM table2";
$mysqli->query($sql);

To combine tables using UNION operator through a PHP program, we need to execute the SQL statement with UNION operator using the mysql2 function named query() as follows −

sql= " SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2";
con.query(sql);

To combine tables using UNION operator through a PHP program, we need to execute the SQL statement with UNION operator using the JDBC type 4 driver function named executeQuery() as follows −

String sql = "SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2";
statement.executeQuery(sql);

To combine tables using UNION operator through a PHP program, we need to execute the SQL statement with UNION operator using the MySQL Connector/Python function named execute() as follows −

union_query = "SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2"
cursorObj.execute(union_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 fname, lname, addr FROM prospect  UNION  SELECT first_name, last_name, address FROM customer  UNION  SELECT company, '''', street FROM vendor;";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("First Name %s, Last Name: %s, Address %s",
      $row["fname"],
      $row["lname"],
      $row["addr"],);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
First Name Peter, Last Name: Jones, Address 482 Rush St., Apt. 402
First Name Bernice, Last Name: Smith, Address 916 Maple Dr.
First Name Grace, Last Name: Peterson, Address 16055 Seminole Ave.
First Name Walter, Last Name: Brown, Address 8602 1st St.
First Name ReddyParts, Inc., Last Name: , Address 38 Industrial Blvd.
First Name Parts-to-go, Ltd., Last Name: , Address 213B Commerce Park.

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 PROSPECT table
  sql = "CREATE TABLE PROSPECT( fname varchar(400), lname varchar(400), addr varchar(200));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO PROSPECT (fname, lname, addr) VALUES (''peter'', ''Jones'', ''482 Rush St., Apt. 402''), (''Bernice'', ''Smith'', ''916 Maple Dr.'');"
  con.query(sql);

  //Creating CUSTOMER table
  sql = "CREATE TABLE CUSTOMER( last_name varchar(400), first_name varchar(400), address varchar(200));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMER (last_name, first_name, address) VALUES (''Peterson'', ''Grace'', ''16055 Seminole Ave.''), (''Smith'', ''Bernice'', ''916 Maple Dr.''), (''Brown'', ''Walter'', ''8602 1st St.'');"
  con.query(sql);

  //Creating vendor table
  sql = "CREATE TABLE vendor( company varchar(400), street varchar(400));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO vendor (company, street) VALUES (''ReddyParts, Inc.'', ''38 Industrial Blvd.''), (''Parts-to-go, Ltd.'', ''213B Commerce Park.'');"
  con.query(sql);

  //Using UNION
  sql = "SELECT fname, lname, addr FROM prospect UNION SELECT first_name, last_name, address FROM customer UNION SELECT company, '''', street FROM vendor;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  { fname: ''peter'', lname: ''Jones'', addr: ''482 Rush St., Apt. 402'' },
  { fname: ''Bernice'', lname: ''Smith'', addr: ''916 Maple Dr.'' },
  { fname: ''Grace'', lname: ''Peterson'', addr: ''16055 Seminole Ave.'' },
  { fname: ''Walter'', lname: ''Brown'', addr: ''8602 1st St.'' },
  { fname: ''ReddyParts, Inc.'', lname: '''', addr: ''38 Industrial Blvd.'' },
  { fname: ''Parts-to-go, Ltd.'', lname: '''', addr: ''213B Commerce Park.'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class UnionOperator {
    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 fname, lname, addr FROM prospect UNION SELECT first_name, last_name, address FROM customer UNION SELECT company, '''', street FROM vendor";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
                String fname = rs.getString("fname");
                String lname = rs.getString("lname");
                String addr = rs.getString("addr");
                System.out.println("First Name: " + fname + ", Last Name: " + lname + ", Address: " + addr);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Output

The output obtained is as shown below −

Table records:
First Name: Peter, Last Name: Jones, Address: 482 Rush St., Apt. 402
First Name: Bernice, Last Name: Smith, Address: 916 Maple Dr.
First Name: Grace, Last Name: Peterson, Address: 16055 Seminole Ave.
First Name: Walter, Last Name: Brown, Address: 8602 1st St.
First Name: ReddyParts, Inc., Last Name: , Address: 38 Industrial Blvd.
First Name: Parts-to-go, Ltd., Last Name: , Address: 213B Commerce Park.

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
union_query = f"""
SELECT fname, lname, addr FROM prospect
    UNION
    SELECT first_name, last_name, address FROM customer
    UNION
    SELECT company, '''', street FROM vendor;
"""
cursorObj.execute(union_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 −

(''Peter'', ''Jones'', ''482 Rush St., Apt. 402'')
(''Bernice'', ''Smith'', ''916 Maple Dr.'')
(''Grace'', ''Peterson'', ''16055 Seminole Ave.'')
(''Walter'', ''Brown'', ''8602 1st St.'')
(''ReddyParts, Inc.'', '''', ''38 Industrial Blvd.'')
(''Parts-to-go, Ltd.'', '''', ''213B Commerce Park.'')

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – MINUS Operator nhận dự án làm có lương

MySQL – MINUS Operator

Table of content

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

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – IS NOT NULL Operator nhận dự án làm có lương

MySQL – IS NOT NULL Operator

Table of content

A NULL value in a MySQL table indicates a missing or unknown value. It appears to be blank and does not contain any data. This is different from zero values.

It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. To check for NULL values in a table column, we can use two basic operators:

IS NULL
IS NOT NULL

MySQL IS NOT NULL Operator

The MySQL IS NOT NULL operator is used to verify whether a particular column has a non-null value or not. This operator can be used with SQL statements such as SELECT, UPDATE, and DELETE.

By using the IS NOT NULL operator in a conditional clause, we can only fetch the records that contain valid data in a particular column.

Syntax

Following is the syntax of IS NOT NULL in MySQL −

SELECT column_name1, column_name2, ...
FROM table_name
WHERE column_name IS NOT 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,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

In the following query, we are using the INSERT statement to insert values to the table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', NULL),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', NULL, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', NULL),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', NULL, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table is created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	Delhi	1500.00
3	Kaushik	NULL	Kota	2000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
6	Komal	NULL	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Example

In the following query, we are going to return all the records from the CUSTOMERS table where the AGE is not NULL.

SELECT * FROM CUSTOMERS
WHERE AGE IS NOT NULL;

Output

Following output is produced −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

IS NOT NULL with COUNT() function

We can use the IS NOT NULL operator along with the MySQL COUNT() function to count only the non-null values in a specific column(s).

Syntax

Following is the syntax of the IS NOT NULL with COUNT() function in MySQL −

SELECT COUNT(column_name1, column_name2, ...)
FROM table_name
WHERE condition IS NOT NULL;

Example

The following query returns the count of all rows in the CUSTOMERS table where the ADDRESS column is not NULL.

SELECT COUNT(*) FROM CUSTOMERS
WHERE ADDRESS IS NOT NULL;

Output

On executing the above query, it will generate an output as shown below −

COUNT(*)
5

IS NOT NULL with UPDATE statement

In MySQL, we can update all the non-null rows in a specific column(s) using the UPDATE statement with IS NOT NULL operator.

Syntax

Following is the syntax of the IS NOT NULL operator with the UPDATE statement in MySQL –

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE columnname1, columnname2, ... IS NOT NULL;

Example

In the following query, we will update the SALARY column to a value of 9000 for all records where the SALARY column is not NULL −

UPDATE CUSTOMERS
SET SALARY = 20000
WHERE SALARY IS NOT NULL;

Verification

To check whether the table has been updated or not, execute the SELECT query to display the CUSTOMERS table.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	Delhi	20000.00
3	Kaushik	NULL	Kota	20000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	20000.00
6	Komal	NULL	Hyderabad	20000.00
7	Muffy	24	Indore	20000.00

IS NOT NULL with DELETE statement

In MySQL, we can delete all the non-null rows in a specific column(s) using the DELETE statement with IS NOT NULL operator.

Syntax

Following is the syntax of the IS NOT NULL operator with the DELETE statement in MySQL –

DELETE FROM table_name
WHERE columnname1, columnname2, ... IS NOT NULL;

Example

In the following query, we are trying to delete records which are not null in the AGE column of CUSTOMERS table.

DELETE FROM CUSTOMERS
WHERE AGE IS NOT NULL;

Verification

To verify whether the table has been updated or not, display the table using a SELECT query.

ID	NAME	AGE	ADDRESS	SALARY
3	Kaushik	NULL	Kota	20000.00
6	Komal	NULL	Hyderabad	20000.00

IS NOT NULL Operator Using Client Program

In addition to executing the IS NOT NULL Operator on a MySQL Server using SQL query, we can also execute it using a client program.

Syntax

Following are the syntaxes of the IS NOT NULL Operator in MySQL table in various programming languages −

To execute the IS NOT NULL Operator in MySQL through a PHP program, we need to execute the SQL query with IS NOT NULL operator using the mysqli function named query() as −

$sql = "SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name IS NOT NULL";
$mysqli->query($sql);

To execute the IS NOT NULL Operator in MySQL through a JavaScript program, we need to execute the SQL query with IS NOT NULL operator using the mysql2 function named query() as −

sql= " SELECT column_name1, column_name2, ...FROM table_name
WHERE column_name IS NOT NULL";
con.query(sql);

To execute the IS NOT NULL Operator in MySQL through a Java program, we need to execute the SQL query with IS NOT NULL operator using the function named executeQuery() provided by JDBC type 4 driver −

String sql = "SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name IS NOT NULL";
statement.executeQuery(sql);

To execute the IS NOT NULL Operator in MySQL through a Python program, we need to execute the SQL query with IS NOT NULL operator using the function named execute() provided by MySQL Connector/Python −

is_not_null_query  = "SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name IS NOT NULL"
cursorObj.execute(is_not_null_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  WHERE AGE IS NOT NULL";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",
      $row["ID"],
      $row["NAME"],
      $row["AGE"],
      $row["ADDRESS"],
      $row["SALARY"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
Id 1, Name: Ramesh, Age: 32, Address Hyderabad, Salary 0.000000
Id 2, Name: Khilan, Age: 25, Address , Salary 1500.000000
Id 4, Name: Chaital, Age: 25, Address Mumbai, Salary 0.000000
Id 5, Name: Hardik, Age: 27, Address Vishakapatnam, Salary 8500.000000
Id 7, Name: Muffy, Age: 24, Address , Salary 10000.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(ID INT NOT NULL,NAME VARCHAR(20),AGE INT,ADDRESS CHAR(25),SALARY DECIMAL(18, 2),PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,''Ramesh'', 32, ''Hyderabad'',NULL),(2,''Khilan'', 25, NULL, 1500.00),(3,''kaushik'', NULL, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', NULL),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',NULL, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, NULL, 10000.00);"
  con.query(sql);

  //Using NOT EQUAL Operator
  sql = "SELECT * FROM CUSTOMERS WHERE AGE IS NOT NULL;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {ID: 1, NAME: ''Ramesh'', AGE: 32, ADDRESS: ''Hyderabad'', SALARY: null},
  { ID: 2, NAME: ''Khilan'', AGE: 25, ADDRESS: null, SALARY: ''1500.00'' },
  { ID: 4, NAME: ''Chaital'', AGE: 25, ADDRESS: ''Mumbai'', SALARY: null },
  { ID: 5, NAME: ''Hardik'', AGE: 27, ADDRESS: ''Vishakapatnam'',SALARY: ''8500.00''  },
  { ID: 7, NAME: ''Muffy'', AGE: 24, ADDRESS: null, SALARY: ''10000.00'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class IsNotNullOperator {
  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 WHERE AGE IS NOT NULL";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String age = rs.getString("Age");
              String address = rs.getString("Address");
              String salary = rs.getString("Salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Addresss: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table records:
Id: 1, Name: Ramesh, Age: 32, Addresss: Hyderabad, Salary: null
Id: 2, Name: Khilan, Age: 25, Addresss: null, Salary: 1500.00
Id: 4, Name: Chaital, Age: 25, Addresss: Mumbai, Salary: null
Id: 5, Name: Hardik, Age: 27, Addresss: Vishakapatnam, Salary: 8500.00
Id: 7, Name: Muffy, Age: 24, Addresss: null, Salary: 10000.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
is_not_null_query = f"""
SELECT * FROM CUSTOMERS
WHERE AGE IS NOT NULL;
"""
cursorObj.execute(is_not_null_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 −

(1, ''Ramesh'', 32, ''Hyderabad'', None)
(2, ''Khilan'', 25, None, Decimal(''1500.00''))
(4, ''Chaital'', 25, ''Mumbai'', None)
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''8500.00''))
(7, ''Muffy'', 24, None, Decimal(''10000.00''))

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – IS NULL Operator nhận dự án làm có lương

MySQL – IS NULL Operator

Table of content

NULL values in a MySQL table fields indicate that no (or unknown) values are present in them. These values are different from zeroes or invalid values.

In MySQL, it is not possible to check NULL values with comparison operators such as =, <, or <>. Instead, we use the IS NULL and IS NOT NULL (filtering non-null values) operators.

MySQL IS NULL Operator

The IS NULL operator in MySQL is used to check whether a value in a column is NULL. Using the IS NULL operator with a conditional clause allows us to filter records that contain NULL values in a particular column.

We can also use this operator with SELECT, UPDATE, and DELETE SQL statements.

Syntax

Following is the syntax of IS NULL in MySQL −

SELECT column_name1, column_name2, ...
FROM table_name
WHERE 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,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

In the following query, we are using the INSERT statement to insert values to the table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', NULL),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', NULL, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', NULL),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', NULL, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table is created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	Delhi	1500.00
3	Kaushik	NULL	Kota	2000.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
6	Komal	NULL	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

IS NULL with SELECT statement

The MySQL IS NULL operator can be used with the SELECT statement to filter the records with NULL values.

Example

In the following query, we are going to return all the records from the CUSTOMERS table where the AGE is null.

SELECT * FROM CUSTOMERS
WHERE AGE IS NULL;

Output

On executing the above query, it will generate an output as shown below −

ID	NAME	AGE	ADDRESS	SALARY
3	Kaushik	NULL	Kota	20000.00
6	Komal	NULL	Hyderabad	20000.00

IS NULL with COUNT() function

We can use the MySQL IS NULL operator with the COUNT() function to count the number of records with NULL values in a particular column.

Syntax

Following is the syntax of the IS NULL with COUNT() function in MySQL −

SELECT COUNT(column_name1, column_name2, ...)
FROM table_name
WHERE condition IS NULL;

Example

The following query returns the count of records have a blank field (NULL) in ADDRESS column of the CUSTOMERS table.

SELECT COUNT(*) FROM CUSTOMERS
WHERE ADDRESS IS NULL;

Output

On executing the above query, it will generate an output as shown below −

COUNT(*)
2

IS NULL with UPDATE statement

In MySQL, we can use the IS NULL operator with the UPDATE statement to update records with NULL values in a particular column.

Syntax

Following is the syntax of the IS NULL operator with the UPDATE statement in MySQL –

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE columnname1, columnname2, ... IS NULL;

Example

In the following query, we are updating the blank (NULL) records of the SALARY column to a value of 9000.

UPDATE CUSTOMERS
SET SALARY = 9000
WHERE SALARY IS NULL;

Verification

To check whether the table has been updated or not, execute the SELECT query to display the table.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	9000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	NULL	Kota	2000.00
4	Chaitali	25	Mumbai	9000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	NULL	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

IS NULL with DELETE statement

In MySQL, we can use the IS NULL operator with the DELETE statement to delete records with NULL values in a particular column.

Syntax

Following is the syntax of the IS NULL operator with the DELETE statement in MySQL –

DELETE FROM table_name
WHERE column_name(s) IS NULL;

Example

In the following query, we are trying to delete the blank (NULL) records present in the ADDRESS column of CUSTOMERS table.

DELETE FROM CUSTOMERS
WHERE AGE IS NULL;

Verification

To check whether the table has been changed or not, execute the SELECT query to display the table.

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	NULL
2	Khilan	25	Delhi	1500.00
4	Chaitali	25	Mumbai	NULL
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

IS NULL Operator Using Client Program

In addition to executing the IS NULL Operator on a MySQL Server using SQL query, we can also execute it using a client program.

Syntax

Following are the syntaxes of the IS NULL Operator in MySQL table in various programming languages −

To execute the IS NULL Operator in MySQL through a PHP program, we need to execute the SQL query with IS NULL operator using the mysqli function named query() as −

$sql = "SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name IS NULL";
$mysqli->query($sql);

To execute the IS NULL Operator in MySQL through a JavaScript program, we need to execute the SQL query with IS NULL operator using the mysql2 function named query() as −

sql= " SELECT column_name1, column_name2, ...FROM table_name
WHERE column_name IS NULL";
con.query(sql);

To execute the IS NULL Operator in MySQL through a Java program, we need to execute the SQL query with IS NULL operator using the function named executeQuery() provided by JDBC type 4 driver −

String sql = "SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name IS NULL";
statement.executeQuery(sql);

To execute the IS NULL Operator in MySQL through a Python program, we need to execute the SQL query with IS NULL operator using the function named execute() provided by MySQL Connector/Python −

is_null_query = "SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name IS NULL"
cursorObj.execute(is_null_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 WHERE AGE IS NULL";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",
      $row["ID"],
      $row["NAME"],
      $row["AGE"],
      $row["ADDRESS"],
      $row["SALARY"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
Id 3, Name: kaushik, Age: 0, Address Hyderabad, Salary 2000.000000
Id 6, Name: Komal, Age: 0, Address Vishakapatnam, Salary 4500.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(ID INT NOT NULL,NAME VARCHAR(20),AGE INT,ADDRESS CHAR(25),SALARY DECIMAL(18, 2),PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,''Ramesh'', 32, ''Hyderabad'',NULL),(2,''Khilan'', 25, NULL, 1500.00),(3,''kaushik'', NULL, ''Hyderabad'', 2000.00),(4,''Chaital'', 25, ''Mumbai'', NULL),(5,''Hardik'', 27, ''Vishakapatnam'', 8500.00),(6, ''Komal'',NULL, ''Vishakapatnam'', 4500.00),(7, ''Muffy'',24, NULL, 10000.00);"
  con.query(sql);

  //Using IS NULL Operator
  sql = "SELECT * FROM CUSTOMERS WHERE AGE IS NULL;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: null,
    ADDRESS: ''Hyderabad'',
    SALARY: ''2000.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: null,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''4500.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class IsNullOperator {
  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 WHERE AGE IS NULL";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String age = rs.getString("Age");
              String address = rs.getString("Address");
              String salary = rs.getString("Salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Addresss: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table records:
Id: 3, Name: kaushik, Age: null, Addresss: Hyderabad, Salary: 2000.00
Id: 6, Name: Komal, Age: null, Addresss: Vishakapatnam, Salary: 4500.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
is_null_query = f"""
SELECT * FROM CUSTOMERS
WHERE AGE IS NULL;
"""
cursorObj.execute(is_null_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 −

(3, ''kaushik'', None, ''Hyderabad'', Decimal(''2000.00''))
(6, ''Komal'', None, ''Vishakapatnam'', Decimal(''4500.00''))

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – Between Operator nhận dự án làm có lương

MySQL – Between Operator

Table of content

MySQL Between Operator

The BETWEEN operator in MySQL is a logical operator provided by SQL, that is used to restrict the range from which the data values are to be retrieved. The retrieved values can be integers, characters, or dates.

You can use BETWEEN operator to replace a combination of “greater than equal AND less than equal” conditions.

Let us understand in a better way by using the following example table −

Syntax

Following is the syntax of the BETWEEN operator in MySQL −

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Here,

value1 is the beginning value of the range.
value2 is the ending value of the range (inclusive).

Example

First of all, 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)
);

Let us insert some values into this table using the following INSERT query −

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 created is as follows −

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, we are using the BETWEEN operator to retrieve the details of the CUSTOMERS whose AGE (numeric data) is between 20 and 25 −

SELECT * FROM CUSTOMERS
WHERE AGE BETWEEN 20 AND 25;

Output

When we execute the above query, the output is obtained as follows −

ID	NAME	AGE	ADDRESS	SALARY
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

MySQL BETWEEN with IN Operator

The BETWEEN operator and the IN operator can be used together in a MySQL query, to select values that are within a specified range and also match with specified values.

Syntax

Following is the syntax of the BETWEEN operator in MySQL −

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2
AND column_name IN (list_of_values);

Example

In this example, we are selecting all the customers whose salary is between 4000 and 10000. In addition; we are only retrieving the customers who are living in MP and Bhopal using IN operator in SQL.

SELECT * FROM CUSTOMERS
WHERE SALARY BETWEEN 4000 AND 10000
AND ADDRESS IN (''Hyderabad'', ''Bhopal'');

Output

The following is obtained −

ID	NAME	AGE	ADDRESS	SALARY
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00

MySQL BETWEEN with UPDATE statement

The UPDATE statement in MySQL is used to modify existing data in a database table. Using the BETWEEN operator in an UPDATE statement to update values within the specified range.

Example

Let us update the salaries of the customers whose age lies between 25 to 30 using the following query −

UPDATE CUSTOMERS
SET SALARY = 10000
WHERE AGE BETWEEN 20 AND 25;

Verification

Let us verify whether the salaries are updated or not using the following query −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	10000.00
3	Kaushik	23	Kota	10000.00
4	Chaitali	25	Mumbai	10000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	10000.00
7	Muffy	24	Indore	10000.00

BETWEEN operator with DELETE statement

We can also use the BETWEEN operator in a DELETE statement to delete rows within a specified range.

Example

Now, let us delete the customers whose age is between 18 and 20 using the DELETE command.

DELETE FROM CUSTOMERS
WHERE AGE BETWEEN 20 AND 22;

Verification

Let us verify whether the specified aged employees are deleted or not using the following query −

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
7	Muffy	24	Indore	10000.00

MySQL NOT BETWEEN Operator

The NOT BETWEEN operator in MySQL is a negation of the BETWEEN operator. This is used to retrieve the data which is not present in the specified range or time interval.

Syntax

Following is the syntax of the NOT BETWEEN operator in MySQL −

SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

Example

Consider the CUSTOMERS table to retrieve the details of customers whose age is not between 20 and 25 (numeric data) using the following query.

SELECT * FROM CUSTOMERS
WHERE AGE NOT BETWEEN 20 AND 25;

Output

Following is the output −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
5	Hardik	27	Bhopal	8500.00

NOT BETWEEN operator with IN operator

Like the BETWEEN operator, we can also use the NOT BETWEEN operator in combination with the IN operator. This is to select values that fall outside a range and also do not match with the specified values.

Example

In the following query, we are selecting the customers whose salary is NOT between 1000 and 5000. In addition; we are not retrieving the employees who are living in Bhopal using IN operator in SQL.

SELECT * FROM CUSTOMERS
WHERE SALARY NOT BETWEEN 1000 AND 5000
AND ADDRESS NOT IN (''Bhopal'');

Output

Following is the output −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
7	Muffy	24	Indore	10000.00

Between Operator Using Client Program

We can also apply the BETWEEN operator on a MySQL table using a client program.

Syntax

Following are the syntaxes of the Between Operator in MySQL table in various programming languages −

To execute the Between Operator in MySQL through a PHP program, we need to execute the SQL query with BETWEEN statement using the mysqli function named query() as −

$sql = "SELECT column1, column2, ... FROM table_name
WHERE column_name BETWEEN value1 AND value2";
$mysqli->query($sql);

To execute the Between Operator in MySQL through a JavaScript program, we need to execute the SQL query with BETWEEN statement using the mysql2 function named query() as −

sql = " SELECT column_name(s) FROM table_name
WHERE column_name BETWEEN value1 AND value2";
con.query(sql);

To execute the Between Operator in MySQL through a Java program, we need to execute the SQL query with BETWEEN statement using the JDBC type 4 driver function named executeQuery() as −

String sql = "SELECT column1, column2, ... FROM table_name
WHERE column_name BETWEEN value1 AND value2";
statement.executeQuery(sql);

To execute the Between Operator in MySQL through a Java program, we need to execute the SQL query with BETWEEN statement using the MySQL Connector/Python function named execute() as −

between_query = "SELECT column1, column2, ... FROM table_name
WHERE column_name BETWEEN value1 AND value2"
cursorObj.execute(between_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 WHERE SALARY BETWEEN 4000 AND 10000";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",
      $row["ID"],
      $row["NAME"],
      $row["AGE"],
      $row["ADDRESS"],
      $row["SALARY"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close()

Output

The output obtained is as follows −

Table records:
Id 5, Name: Hardik, Age: 27, Address Vishakapatnam, Salary 8500.000000
Id 6, Name: Komal, Age: 0, Address Vishakapatnam, Salary 4500.000000
Id 7, Name: Muffy, Age: 24, Address , Salary 10000.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 IF NOT EXISTS employee_tbl(id INT NOT NULL, name VARCHAR(100) NOT NULL, work_date DATE, daily_typing_pages INT);"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO employee_tbl(id, name, work_date, daily_typing_pages) VALUES(1, ''John'', ''2007-01-24'', 250), (2, ''Ram'', ''2007-05-27'', 220), (3, ''Jack'', ''2007-05-06'', 170), (3, ''Jack'', ''2007-04-06'', 100), (4, ''Jill'', ''2007-04-06'', 220),(5, ''Zara'', ''2007-06-06'', 300),(5, ''Zara'', ''2007-02-06'', 350);"
  con.query(sql);

  //Using BETWEEN Clause
  sql = "SELECT * FROM employee_tbl WHERE daily_typing_pages BETWEEN 170 AND 300;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    id: 1,
    name: ''John'',
    work_date: 2007-01-23T18:30:00.000Z,
    daily_typing_pages: 250
  },
  {
    id: 2,
    name: ''Ram'',
    work_date: 2007-05-26T18:30:00.000Z,
    daily_typing_pages: 220
  },
  {
    id: 3,
    name: ''Jack'',
    work_date: 2007-05-05T18:30:00.000Z,
    daily_typing_pages: 170
  },
  {
    id: 4,
    name: ''Jill'',
    work_date: 2007-04-05T18:30:00.000Z,
    daily_typing_pages: 220
  },
  {
    id: 5,
    name: ''Zara'',
    work_date: 2007-06-05T18:30:00.000Z,
    daily_typing_pages: 300
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class BetweenClause {
    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";
            rs = st.executeQuery(sql);
            System.out.println("Table records before update: ");
            while(rs.next()) {
                String id = rs.getString("Id");
                String name = rs.getString("Name");
                String age = rs.getString("Age");
                String address = rs.getString("Address");
                String salary = rs.getString("Salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Addresss: " + address + ", Salary: " + salary);
            }
            String sql1 = "UPDATE CUSTOMERS SET SALARY = 10000 WHERE AGE BETWEEN 20 AND 25";
            st.executeUpdate(sql1);
            String sql2 = "SELECT * FROM CUSTOMERS";
            rs = st.executeQuery(sql2);
            System.out.println("Table records after update: ");
            while(rs.next()) {
                String id = rs.getString("Id");
                String name = rs.getString("Name");
                String age = rs.getString("Age");
                String address = rs.getString("Address");
                String salary = rs.getString("Salary");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Addresss: " + address + ", Salary: " + salary);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Output

The output obtained is as shown below −

Table records before update:
Id: 1, Name: Ramesh, Age: 32, Addresss: Hyderabad, Salary: null
Id: 2, Name: Khilan, Age: 25, Addresss: null, Salary: 10000.00
Id: 3, Name: kaushik, Age: null, Addresss: Hyderabad, Salary: 2000.00
Id: 4, Name: Chaital, Age: 25, Addresss: Mumbai, Salary: 10000.00
Id: 5, Name: Hardik, Age: 27, Addresss: Vishakapatnam, Salary: 8500.00
Id: 6, Name: Komal, Age: null, Addresss: Vishakapatnam, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Addresss: null, Salary: 10000.00
Table records after update:
Id: 1, Name: Ramesh, Age: 32, Addresss: Hyderabad, Salary: null
Id: 2, Name: Khilan, Age: 25, Addresss: null, Salary: 10000.00
Id: 3, Name: kaushik, Age: null, Addresss: Hyderabad, Salary: 2000.00
Id: 4, Name: Chaital, Age: 25, Addresss: Mumbai, Salary: 10000.00
Id: 5, Name: Hardik, Age: 27, Addresss: Vishakapatnam, Salary: 8500.00
Id: 6, Name: Komal, Age: null, Addresss: Vishakapatnam, Salary: 4500.00
Id: 7, Name: Muffy, Age: 24, Addresss: null, Salary: 10000.0

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
between_query = f"""
SELECT * FROM CUSTOMERS WHERE SALARY BETWEEN 4000 AND 10000;
"""
cursorObj.execute(between_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 −

(1, ''Ramesh'', 32, ''Hyderabad'', Decimal(''4000.00''))
(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.00''))
(7, ''Muffy'', 24, ''Delhi'', Decimal(''10000.00''))

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – INTERSECT Operator nhận dự án làm có lương

MySQL – Intersect Operator

Table of content

In mathematical set theory, the intersection of two sets is a set that contains only those elements that are common to both sets. In other words, the intersection of two sets is a set of elements that exist in both sets.

If we perform the intersection operation on both sets using the INTERSECT operator, it displays the common rows from both tables. This operator removes the duplicate rows from the final result set.

MySQL INTERSECT Operator

In MySQL, the INTERSECT operator is used to return the records that are identical/common between the result sets of two SELECT (tables) statements.

However, the INTERSECT operator works only if both the SELECT statements have an equal number of columns with same data types and names.

Syntax

Following is the syntax of INTERSECT operator in MySQL −

SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
INTERSECT
SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN

Example

First of all, let us create a table named STUDENTS using the following query −

CREATE TABLE STUDENTS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   PRIMARY KEY(ID)
);

Here, we are inserting some values into the table using the INSERT statement.

INSERT INTO STUDENTS VALUES
(1, ''Vijay'', ''Cricket'', 18),
(2, ''Varun'', ''Football'', 26),
(3, ''Surya'', ''Cricket'', 19),
(4, ''Karthik'', ''Cricket'', 25),
(5, ''Sunny'', ''Football'', 26),
(6, ''Dev'', ''Cricket'', 23);

The table is created as follows −

ID	NAME	HOBBY	AGE
1	Vijay	Cricket	18
2	Varun	Football	26
3	Surya	Cricket	19
4	Karthik	Cricket	25
5	Sunny	Football	26
6	Dev	Cricket	23

Now, let us create another table with name ASSOCIATES using the following query −

CREATE TABLE ASSOCIATES(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   SUBJECT VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   PRIMARY KEY(ID)
);

Here, we are inserting some values into the table using the INSERT statement −

INSERT INTO ASSOCIATES VALUES
(1, ''Naina'', ''Maths'', 24, ''Cricket''),
(2, ''Varun'', ''Physics'', 26, ''Football''),
(3, ''Dev'', ''Maths'', 23, ''Cricket''),
(4, ''Priya'', ''Physics'', 25, ''Cricket''),
(5, ''Aditya'', ''Chemistry'', 21, ''Cricket''),
(6, ''Kalyan'', ''Maths'', 30, ''Football'');

The table is created as follows −

ID	NAME	SUBJECT	AGE	HOBBY
1	Naina	Maths	24	Cricket
2	Varun	Physics	26	Football
3	Dev	Maths	23	Cricket
4	Priya	Physics	25	Cricket
5	Aditya	Chemistry	21	Cricket
6	Kalyan	Maths	30	Football

Now, we return the common records from both the tables using the following query −

SELECT NAME, AGE, HOBBY FROM STUDENTS
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES;

Output

The output is obtained as follows −

NAME	AGE	HOBBY
Varun	26	Football
Dev	23	Cricket

INTERSECT with BETWEEN Operator

The MySQL INTERSECT operator can be used with the BETWEEN operator to find the rows that exist within the specified range.

Example

In the following query, we are retrieving the records that are common in both tables. In addition; we are retrieving the records who are aged between 25 and 30 −

SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE AGE BETWEEN 25 AND 30
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE AGE BETWEEN 20 AND 30;

Output

On executing the given program, the output is displayed as follows −

NAME	AGE	HOBBY
Varun	26	Football

INTERSECT with IN Operator

In MySQL, we can use the INTERSECT operator with IN operator to find the common rows that have the specified values. The IN operator is used to filter a result set based on a list of specified values.

Example

In the following query, we are trying to return the common records from both tables. In addition; we are using th IN operator to retrieve the records whose hobby is “Cricket”.

SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE HOBBY IN(''Cricket'')
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE HOBBY IN(''Cricket'');

Output

The output for the program above is produced as given below −

NAME	AGE	HOBBY
Dev	23	Cricket

INTERSECT with LIKE Operator

The LIKE operator is used to perform pattern matching on a string value.

We can use the LIKE operator with the INTERSECT operator in MySQL to find the common rows that match the specified pattern.

Example

In the following query, we are using the wildcard ”%” with the LIKE operator to fetch the names with ”v” from the common names of both tables.

SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE NAME LIKE ''v%''
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE NAME LIKE ''v%

Output

Let us compile and run the program, to produce the following result −

NAME	AGE	HOBBY
Varun	26	Football

Intersect Operator Using Client Program

In addition to executing the Intersect Operator in MySQL server, we can also execute the INTERSECT operator on a table using a client program.

Syntax

Following are the syntaxes of the Intersect Operator in MySQL table in various programming languages −

To execute the Intersect operator in MySQL table through a PHP program, we need to execute INTERSECT statement using the query() function of mysqli connector.

$sql = "SELECT column1, column2,..., columnN FROM table1, table2,...,
tableN INTERSECT SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN";
$mysqli->query($sql);

To execute the Intersect operator in MySQL table through a JavaScript program, we need to execute INTERSECT statement using the query() function of mysql2 connector.

sql = "SELECT column1, column2,..., columnN FROM table1, table2,..., tableN
INTERSECT SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN";
con.query(sql);

To execute the Intersect operator in MySQL table through a Java program, we need to execute INTERSECT statement using the executeQuery() function of JDBC type 4 driver.

String sql = "SELECT column1, column2,..., columnN FROM table1, table2,..., tableN
INTERSECT SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN";
statement.executeQuery(sql);

To execute the Intersect operator in MySQL table through a Python program, we need to execute INTERSECT statement using the execute() function provided by MySQL Connector/Python.

intersect_query = SELECT column1, column2,..., column
FROM table1, table2,..., tableN
INTERSECT SELECT column1, column2,..., column
FROM table1, table2,..., tableN
cursorObj.execute(intersect_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 NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES;";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("NAME %s, AGE %d, HOBBY %s",
      $row["NAME"],
      $row["AGE"],
      $row["HOBBY"],);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
NAME Varun, AGE 26, HOBBY Football
NAME Dev, AGE 23, HOBBY Cricket

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 STUDENTS table
  sql = "CREATE TABLE STUDENTS(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,HOBBY VARCHAR(20) NOT NULL,AGE INT NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(1, ''Vijay'', ''Cricket'',18),(2, ''Varun'',''Football'', 26),(3, ''Surya'', ''Cricket'',19),(4, ''Karthik'',''Cricket'', 25),(5, ''Sunny'',''Football'', 26),(6, ''Dev'', ''Cricket'',23);"
  con.query(sql);

  //Creating ASSOCIATES table
  sql = "CREATE TABLE ASSOCIATES(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,SUBJECT VARCHAR(20) NOT NULL,AGE INT NOT NULL,HOBBY VARCHAR(20) NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(1, ''Naina'',''Maths'', 24, ''Cricket''),(2, ''Varun'',''Physics'', 26, ''Football''),(3, ''Dev'',''Maths'', 23, ''Cricket''),(4, ''Priya'',''Physics'', 25, ''Cricket''),(5,''Aditya'', ''Chemistry'', 21, ''Cricket''),(6,''Kalyan'', ''Maths'', 30, ''Football'');"
  con.query(sql);

  //Using INTERSECT Operator
  sql = "SELECT NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  { NAME: ''Varun'', AGE: 26, HOBBY: ''Football'' },
  { NAME: ''Dev'', AGE: 23, HOBBY: ''Cricket'' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class IntersectOperator {
  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 NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String name = rs.getString("NAME");
              String age = rs.getString("AGE");
              String hobby = rs.getString("HOBBY");
              System.out.println("Name: " + name + ", Age: " + age + ", Hobby: " + hobby);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table records:
Name: Varun, Age: 26, Hobby: Football
Name: Dev, Age: 23, Hobby: Cricket

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
intersect_query = f"""
SELECT NAME, AGE, HOBBY FROM STUDENTS
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES;
"""
cursorObj.execute(intersect_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 −

(''Varun'', 26, ''Football'')
(''Dev'', 23, ''Cricket'')

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – NOT EQUAL Operator nhận dự án làm có lương

MySQL – NOT EQUAL Operator

Table of content

MySQL NOT EQUAL Operator

The MySQL NOT EQUAL operator is used to compare two values and return true if they are not equal. It is represented by “<>” and “!=”. The difference between these two is that <> follows the ISO standard, but != doesn”t. So, it is recommended to use the <> operator.

We can use this operator in WHERE clauses to filter records based on a specific condition and in GROUP BY clauses to group results.

Note: The comparison is case-sensitive by default when using this operator with text values.

Syntax

Following is the syntax of the NOT EQUAL operator in MySQL −

SELECT column1, column2, ...
FROM table_name
WHERE column_name <> value;

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)
);

The below query uses INSERT INTO statement to add 7 records into above-created table −

INSERT INTO CUSTOMERS (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, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to retrieve all the records present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

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

NOT EQUAL with String Values

In MySQL, we can also use the NOT EQUAL to compare two string values. It returns true if both values are not equal. We can use “<>” or “!=” in the WHERE clause of a SQL statement and exclude rows that match a specific value.

Example

In the following query, we are selecting all the records from the CUSTOMERS table whose NAME is not “Khilan”.

SELECT * FROM CUSTOMERS WHERE NAME <> "Khilan";

Output

The output of the above code is as shown below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

NOT EQUAL with GROUP BY Clause

MySQL”s NOT EQUAL operator can be used along with the GROUP BY clause. It will group the results by the values that are not equal to the specified text value.

The aggregate functions such as COUNT(), MAX(), MIN(), SUM(), and AVG() are frequently used with the GROUP BY statement.

Example

In this query, we are counting the number of records with distinct ”ID” values for each ”AGE” in the ”CUSTOMERS” table. We are excluding records where ”AGE” is equal to ”22”, and grouping the results based on the ”AGE” column.

SELECT COUNT(ID), AGE FROM CUSTOMERS
WHERE AGE  ''22'' GROUP BY AGE;

Output

COUNT(ID)	AGE
1	32
2	25
1	23
1	27
1	24

NOT EQUAL with Multiple Conditions

Depending on the situation, the NOT EQUAL operator can be used with multiple conditions in a WHERE clause to filter out rows that match specific criteria.

Example

Here, we are going to select all the customers whose salary is either “>2000” or “=2000”. At the same time, the customer must not be from “Bhopal”.

SELECT * FROM CUSTOMERS
WHERE ADDRESS  ''Bhopal'' AND (SALARY>''2000'' OR SALARY=''2000'');

Output

When we execute the query above, the output is obtained as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Negating a Condition Using NOT EQUAL

The MySQL NOT EQUAL operator can be combined with the NOT operator to negate a condition and filter out rows that meet a specific condition.

Example

The following query retrieves all rows from the “CUSTOMERS” table where the “SALARY” is equal to ”2000” −

SELECT * FROM CUSTOMERS
WHERE NOT SALARY != ''2000

Output

When the query gets executed it will generate the following output as shown below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00

NOT EQUAL Operator Using a Client Program

Besides using MySQL queries to perform the NOT EQUAL operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

To perform the NOT EQUAL Operator on a MySQL table through a PHP program, we need to execute SELECT statement with NOT EQUAL Operator using the mysqli function query() as follows −

$sql = "SELECT column1, column2, ... FROM table_name
WHERE column_name  value";
$mysqli->query($sql);

To perform the NOT EQUAL Operator on a MySQL table through a Node.js program, we need to execute SELECT statement with NOT EQUAL Operator using the query() function of the mysql2 library as follows −

sql= "SELECT column1, column2, ... FROM table_name
WHERE column_name  value";
con.query(sql);

To perform the NOT EQUAL Operator on a MySQL table through a Java program, we need to execute SELECT statement with NOT EQUAL Operator using the JDBC function executeUpdate() as follows −

String sql = "SELECT column1, column2, ... FROM table_name
WHERE column_name  value";
statement.executeQuery(sql);

To perform the NOT EQUAL Operator on a MySQL table through a Python program, we need to execute SELECT statement with NOT EQUAL Operator using the execute() function of the MySQL Connector/Python as follows −

not_equal_query = "SELECT column1, column2, ... FROM table_name
WHERE column_name  value"
cursorObj.execute(not_equal_query);

Example

Following are the programs −

$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 WHERE NAME  ''Muffy''";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",
      $row["ID"],
      $row["NAME"],
      $row["AGE"],
      $row["ADDRESS"],
      $row["SALARY"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
Id 1, Name: Ramesh, Age: 32, Address Hyderabad, Salary 4000.000000
Id 2, Name: Khilan, Age: 25, Address Kerala, Salary 8000.000000
Id 3, Name: kaushik, Age: 23, Address Hyderabad, Salary 11000.000000
Id 4, Name: Chaital, Age: 25, Address Mumbai, Salary 1200.000000
Id 5, Name: Hardik, Age: 27, Address Vishakapatnam, Salary 10000.000000
Id 6, Name: Komal, Age: 29, Address Vishakapatnam, Salary 7000.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 (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,''Ramesh'', 32, ''Hyderabad'',4000.00),(2,''Khilan'', 25, ''Kerala'', 8000.00),(3,''kaushik'', 23, ''Hyderabad'', 11000.00),(4,''Chaital'', 25, ''Mumbai'', 1200.00),(5,''Hardik'', 27, ''Vishakapatnam'', 10000.00),(6, ''Komal'',29, ''Vishakapatnam'', 7000.00),(7, ''Muffy'',24, ''Delhi'', 10000.00);"
  con.query(sql);

  //Using NOT EQUAL Operator
  sql = "SELECT * FROM CUSTOMERS WHERE NAME  ''Muffy"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 1,
    NAME: ''Ramesh'',
    AGE: 32,
    ADDRESS: ''Hyderabad'',
    SALARY: ''4000.00''
  },
  {
    ID: 2,
    NAME: ''Khilan'',
    AGE: 25,
    ADDRESS: ''Kerala'',
    SALARY: ''8000.00''
  },
  {
    ID: 3,
    NAME: ''kaushik'',
    AGE: 23,
    ADDRESS: ''Hyderabad'',
    SALARY: ''11000.00''
  },
  {
    ID: 4,
    NAME: ''Chaital'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''1200.00''
  },
  {
    ID: 5,
    NAME: ''Hardik'',
    AGE: 27,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''10000.00''
  },
  {
    ID: 6,
    NAME: ''Komal'',
    AGE: 29,
    ADDRESS: ''Vishakapatnam'',
    SALARY: ''7000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NotEqualOperator {
  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 WHERE NAME  ''Muffy''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String age = rs.getString("Age");
              String address = rs.getString("Address");
              String salary = rs.getString("Salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Addresss: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table records:
Id: 1, Name: Ramesh, Age: 32, Addresss: Ahmedabad, Salary: 2000.00
Id: 2, Name: Khilan, Age: 30, Addresss: Delhi, Salary: 1500.00
Id: 3, Name: kaushik, Age: 23, Addresss: Kota, Salary: 2000.00
Id: 4, Name: Chaitali, Age: 30, Addresss: Mumbai, Salary: 6500.00
Id: 5, Name: Hardik, Age: 30, Addresss: Bhopal, Salary: 8500.00
Id: 6, Name: Komal, Age: 22, Addresss: MP, Salary: 4500.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
not_equal_query = f"""
SELECT * FROM CUSTOMERS WHERE NAME  ''Muffy
"""
cursorObj.execute(not_equal_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 −

(1, ''Ramesh'', 32, ''Hyderabad'', Decimal(''4000.00''))
(2, ''Khilan'', 25, ''Kerala'', Decimal(''8000.00''))
(3, ''kaushik'', 23, ''Hyderabad'', Decimal(''11000.00''))
(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00''))
(5, ''Hardik'', 27, ''Vishakapatnam'', Decimal(''10000.00''))
(6, ''Komal'', 29, ''Vishakapatnam'', Decimal(''7000.00''))

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Khóa học miễn phí MySQL – EXISTS Operator nhận dự án làm có lương

MySQL – Exists Operator

Table of content

MySQL Exists Operator

The EXISTS operator in MySQL checks for the existence of a record in a table. It”s used in the WHERE clause of a SELECT statement to verify if a subquery returns any rows. It returns TRUE if the subquery returns at least one record, else false.

We can also use the operator with the SQL statements such as SELECT, INSERT, UPDATE, and DELETE to verify the existence of the records in subqueries.

Syntax

Following is the syntax of the EXISTS operator in MySQL −

SELECT column1, column2, ...
FROM table_name
WHERE EXISTS (subquery);

Example

Before performing the EXISTS operator, let us first two different tables named CUSTOMERS and CARS. Here, we are creating the CUSTOMERS table −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following query uses INSERT INTO statement to add 7 records into the above-created table −

INSERT INTO CUSTOMERS (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, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to fetch all the records present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following is the CUSTOMERS table −

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

Let us create another table named CARS, which contains the details such as ID of the customer, NAME and PRICE of the car −

CREATE TABLE CARS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   PRICE INT NOT NULL,
   PRIMARY KEY (ID)
);

The following query inserts 3 records into the above-created table −

INSERT INTO CARS (ID, NAME, PRICE) VALUES
(2, ''Maruti Swift'', 450000),
(4, ''VOLVO'', 2250000),
(7, ''Toyota'', 2400000);

Execute the below query to fetch all the records present in the CARS table −

SELECT * FROM CARS;

Following is the CARS table −

ID	NAME	PRICE
2	Maruti Swift	450000
4	VOLVO	2250000
7	Toyota	2400000

EXISTS operator with SELECT statement

The SELECT statement in MySQL is used to retrieve data from one or more tables. The EXISTS operator can be used with the SELECT statement to check if rows exist that match a specific condition.

Example

Now, let us fetch the list of the customers with the price of the car greater than 2,000,000 −

SELECT * FROM CUSTOMERS
WHERE EXISTS
(SELECT PRICE FROM CARS
WHERE CARS.ID = CUSTOMERS.ID
AND PRICE > 2000000);

Output

On executing the given query, the output is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
7	Muffy	24	Indore	10000.00

EXISTS Operator with UPDATE statement

The MySQL EXISTS operator can be used with the UPDATE statement to update the rows in a table based on the existence of rows matching in another table.

Example

In this query, we are using the EXISTS operator to UPDATE the name ”Kushal” to all of the customers whose ID is equal to the ID of the CARS table −

UPDATE CUSTOMERS
SET NAME = ''Kushal''
WHERE EXISTS
(SELECT NAME FROM CARS
WHERE CUSTOMERS.ID = CARS.ID);

Output

As we can observe the output, 3 rows have been modified −

Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

Verification

To verify whether the changes are reflected in the CUSTOMERS table, execute the following query −

SELECT * FROM CUSTOMERS;

The CUSTOMERS table is displayed as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Kushal	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Kushal	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Kushal	24	Indore	10000.00

EXISTS Operator with DELETE statement

The MySQL EXISTS operator is used with the DELETE statement to delete the rows in a table based on the existence of rows returned by a subquery.

Example

Here, we are deleting all the records from the CUSTOMERS table whose ID is equal to the ID in the CARS table having a price equal to 2,250,000 −

DELETE FROM CUSTOMERS
WHERE EXISTS
(SELECT * FROM CARS
WHERE CARS.ID = CUSTOMERS.ID
AND CARS.PRICE = 2250000);

Output

As we can observe the output, 1 row has been deleted −

Query OK, 1 row affected (0.00 sec)

Verification

We can verify whether the changes have been reflected in the CUSTOMERS table using the following query −

SELECT * FROM CUSTOMERS;

Output

The output for the query above is produced as given below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

NOT Operator with EXISTS Operator

If we use the NOT with EXISTS operator in MySQL, it will select records from one table that do not exist in another table.

Syntax

Following is the syntax of the NOT EXISTS operator in MySQL −

SELECT column1, column2, ...
FROM table_name
WHERE NOT EXISTS (subquery);

Example

In the following query, we are fetching the NAME of the customers who have not bought any car −

SELECT * FROM CUSTOMERS
WHERE NOT EXISTS
(SELECT * FROM CARS
WHERE CUSTOMERS.ID = CARS.ID);

Output

The output for the query above is produced as given below −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
3	Kaushik	23	Kota	2000.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00

Exists Operator Using a Client Program

In addition to verify whether a particular record exists in a MySQL table with a MySQL query, you can also use a client program to perform the EXISTS operation.

Syntax

Following are the syntaxes of this operation in various programming languages −

To verify whether a particular record exists in a MySQL table through a PHP program, we need to execute SELECT statement with EXISTS operator using the mysqli function query() as follows −

$sql = "SELECT column1, column2, ... FROM table_name
WHERE EXISTS (subquery)";
$mysqli->query($sql);

To verify whether a particular record exists in a MySQL table through a Node.js program, we need to execute SELECT statement with EXISTS operator using the query() function of the mysql2 library as follows −

sql= "SELECT column1, column2, ... FROM table_name
WHERE EXISTS (subquery)";
con.query(sql);

To verify whether a particular record exists in a MySQL table through a Java program, we need to execute SELECT statement with EXISTS operator using the JDBC function executeUpdate() as follows −

String sql = "SELECT column1, column2, ... FROM table_name
WHERE EXISTS (subquery)";
statement.executeQuery(sql);

To verify whether a particular record exists in a MySQL table through a Python program, we need to execute SELECT statement with EXISTS operator using the execute() function of the MySQL Connector/Python as follows −

exists_query = "SELECT column1, column2, ... FROM table_name
WHERE EXISTS (subquery)"
cursorObj.execute(exists_query);

Example

Following are the programs −

$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 WHERE EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000);";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("Id %d, Name: %s, Age: %d, Address %s, Salary %f",
      $row["ID"],
      $row["NAME"],
      $row["AGE"],
      $row["ADDRESS"],
      $row["SALARY"]);
      printf("n");
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
Id 4, Name: Chaital, Age: 25, Address Mumbai, Salary 1200.000000
Id 7, Name: Muffy, Age: 24, Address Delhi, Salary 10000.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 (ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS(ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,''Ramesh'', 32, ''Hyderabad'',4000.00),(2,''Khilan'', 25, ''Kerala'', 8000.00),(3,''kaushik'', 23, ''Hyderabad'', 11000.00),(4,''Chaital'', 25, ''Mumbai'', 1200.00),(5,''Hardik'', 27, ''Vishakapatnam'', 10000.00),(6, ''Komal'',29, ''Vishakapatnam'', 7000.00),(7, ''Muffy'',24, ''Delhi'', 10000.00);"
  con.query(sql);

  //Creating CARS table
  sql = "CREATE TABLE CARS(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,PRICE INT NOT NULL,PRIMARY KEY (ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CARS VALUES(2, ''Maruti Swift'', 450000),(4, ''VOLVO'', 2250000),(7, ''Toyota'', 2400000);"
  con.query(sql);

  //Using EXISTS Operator
  sql = "SELECT * FROM CUSTOMERS WHERE EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000);"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  {
    ID: 4,
    NAME: ''Chaital'',
    AGE: 25,
    ADDRESS: ''Mumbai'',
    SALARY: ''1200.00''
  },
  {
    ID: 7,
    NAME: ''Muffy'',
    AGE: 24,
    ADDRESS: ''Delhi'',
    SALARY: ''10000.00''
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class ExistsOperator {
  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 WHERE EXISTS (SELECT PRICE FROM CARS WHERE CARS.ID = CUSTOMERS.ID AND PRICE > 2000000)";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String id = rs.getString("Id");
              String name = rs.getString("Name");
              String age = rs.getString("Age");
              String address = rs.getString("Address");
              String salary = rs.getString("Salary");
              System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Addresss: " + address + ", Salary: " + salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table records:
Id: 4, Name: Chaitali, Age: 30, Addresss: Mumbai, Salary: 6500.00
Id: 7, Name: Muffy, Age: 24, Addresss: Indore, Salary: 10000.00

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
exists_query = f"""
SELECT * FROM CUSTOMERS WHERE EXISTS (SELECT PRICE FROM CARS WHERE
CARS.ID = CUSTOMERS.ID AND PRICE > 2000000); """
cursorObj.execute(exists_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 −

(4, ''Chaital'', 25, ''Mumbai'', Decimal(''1200.00''))
(7, ''Muffy'', 24, ''Delhi'', Decimal(''10000.00''))

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

August 17, 2024

Author: alien

MySQL – Using Joins

Types of Joins

Example

Output

Joins Using a Client Program

Syntax

Example

Output

Output

Output

Output

MySQL – UNION vs UNION ALL

MySQL UNION Operator

Syntax

MySQL UNION ALL Operator

Syntax

MySQL UNION VS MySQL UNION ALL

Example

Combining tables using UNION Operator

Output

Combining tables using UNION ALL Operator

Output

MySQL – UNION Operator

MySQL UNION Operator

Syntax

Example

Output

UNION with WHERE clause

Syntax

Example

Output

UNION with ORDER BY clause

Syntax

Example

Output

UNION with Aliases

Syntax

Example

Output

UNION ALL Operator

Output

UNION Operator Using Client Program

Syntax

Example

Output

Output

Output

Output

MySQL – MINUS Operator

MySQL MINUS Operator

Syntax

Example

Output

Minus Operator Using Client Program

Syntax

Example

Output

Output

Output

Output

MySQL – IS NOT NULL Operator

MySQL IS NOT NULL Operator

Syntax

Example

Example

Output

IS NOT NULL with COUNT() function

Syntax

Example

Output

IS NOT NULL with UPDATE statement

Syntax

Example

Verification

IS NOT NULL with DELETE statement

Syntax

Example

Verification

IS NOT NULL Operator Using Client Program