edu Toidayhoc

Category: mysql

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

    MySQL – Inner Join

    Table of content

    MySQL Inner Join

    MySQL Inner Join is a type of join that is used to combine records from two related tables, based on common columns from both the tables. These tables are joined together on a specific condition. If the records in both tables satisfy the condition specified, they are combined.

    Inner Join

    This is a default join; that is, even if the JOIN keyword is used instead of INNER JOIN, tables are joined using matching records of common columns. They are also referred to as an Equijoin.

    Syntax

    Following is the basic syntax of MySQL Inner Join −

    SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name

    Example

    Creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

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

    Now insert values into this table using the INSERT statement as follows −

    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

    ORDERS Table −

    Let us create another table ORDERS, containing the details of orders made and the date they are made on.

    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 this 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

    Inner Join Query −

    Let us now combine these two tables using the Inner Join query as shown below −

    SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.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

    Joining Multiple Tables Using Inner Join

    Using the Inner Join query, we can also join as many tables as possible.

    However, only two tables can be joined together on a single condition. This process is done sequentially until all the tables are combined.

    Syntax

    Following is the syntax to join more than two tables using Inner Join −

    SELECT column_name1, column_name2...
FROM table_name1
INNER JOIN
table_name2
ON condition_1
INNER JOIN
table_name3
ON condition_2
.
.
.

    Example

    In this example, let us join three tables including CUSTOMERS and ORDERS along with a new table EMPLOYEE. We will first create the EMPLOYEE table using the query below −

    CREATE TABLE EMPLOYEE (
   EID INT NOT NULL,
   EMPLOYEE_NAME VARCHAR (30) NOT NULL,
   SALES_MADE DECIMAL (20)
);

    Now, we can insert values into this empty tables using the INSERT statement as follows −

    INSERT INTO EMPLOYEE VALUES
(102, ''SARIKA'', 4500),
(100, ''ALEKHYA'', 3623),
(101, ''REVATHI'', 1291),
(103, ''VIVEK'', 3426);

    The details of EMPLOYEE table are seen below.

    EID EMPLOYEE_NAME SALES_MADE
    102 SARIKA 4500
    100 ALEKHYA 3623
    101 REVATHI 1291
    103 VIVEK 3426

    Using the following query, we are combining three tables CUSTOMERS, ORDERS and EMPLOYEE.

    SELECT OID, DATE, AMOUNT, EMPLOYEE_NAME FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
INNER JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;

    Output

    The output is obtained as follows −

    OID DATE AMOUNT EMPLOYEE_NAME
    102 2009-10-08 00:00:00 3000.00 SARIKA
    100 2009-10-08 00:00:00 1500.00 ALEKHYA
    101 2009-11-20 00:00:00 1560.00 REVATHI
    103 2008-05-20 00:00:00 2060.00 VIVEK

    Inner Join with WHERE Clause

    Inner Join uses WHERE clause to apply constraints on the records to be retrieved from a table.

    Syntax

    The syntax of Inner Join when used with WHERE clause is given below −

    SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition

    Example

    Consider the previous two tables CUSTOMERS and ORDERS; and join them using the inner join query by applying some constraints using the WHERE clause.

    SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

    Output

    The table is displayed as follows −

    ID NAME DATE AMOUNT
    3 Kaushik 2009-10-08 00:00:00 3000.00
    4 Chaitali 2008-05-20 00:00:00 2060.00

    Inner Join Using a Client Program

    We can also join two or more than two tables by executing Inner Join operation, using a client program.

    Syntax

    To join tables with common fields use Inner Join operation through a PHP program, we need to execute the JOIN clause using the mysqli function query() as follows −

    $sql = ''SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a INNER JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author
$mysqli->query($sql);

    To join tables with common fields use Inner Join operation through a JavaScript program, we need to execute the JOIN clause using the query() function of mysql2 library as follows −

    sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count  FROM tutorials_tbl a
INNER JOIN tcount_tbl b  ON a.tutorial_author = b.tutorial_author";
con.query(sql);

    To join tables with common fields use Inner Join operation through a Java program, we need to execute the JOIN clause using the JDBC function executeQuery() as follows −

    String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a
INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
st.executeQuery(sql);

    To join tables with common fields use Inner Join operation through a Python program, we need to execute the JOIN clause using the execute() function of the MySQL Connector/Python as follows −

    using_inner_join_query = "SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUST_ID"
cursorObj.execute(using_inner_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 INNER 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);
  //inner join
  sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count  FROM tutorials_tbl a INNER 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 InnerJoin {
   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 INNER JOIN...!;
         String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
         ResultSet resultSet = statement.executeQuery(sql);
         System.out.println("Table records after INNER 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 −

    Table records after INNER 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()
inner_join_query = f"""SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUST_ID """
cursorObj.execute(inner_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'', 3000, ''2009-10-08 00:00:00'')
(3, ''kaushik'', 1500, ''2009-10-08 00:00:00'')
(2, ''Khilan'', 1560, ''2009-11-20 00:00:00'')
(4, ''Chaital'', 2060, ''2008-05-20 00:00: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

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

    MySQL – Left Join

    Table of content

    Unlike inner join, which provides the intersection values of two tables, there is another type of join called Outer Join. This outer join provides the collection of matched and unmatched records of two tables in multiple cases.

    MySQL Left Join

    Left Join is a type of outer join that retrieves all the records from the first table and matches them to the records in second table.

    If the records in left table do not have their counterparts in the second table, NULL values are added.

    But, if the number of records in first table is less than the number of records in second table, the records in second table that do not have any counterparts in the first table will be discarded from the result.

    Left Join

    Syntax

    Following is the basic syntax of Left Join in MySQL −

    SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

    Example

    Using the following query, let us create a table named CUSTOMERS, that contains the personal details of customers including their name, age, address and salary.

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

    Now insert values into this table using the INSERT statement as follows −

    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

    Let us create another table ORDERS, containing the details of orders made and the date they are made on.

    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 this 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

    Left Join Query:

    Using the following left join query, we will retrieve the details of customers who made an order at the specified date. If there is no match found, the query below will return NULL in that record.

    SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

    Output

    The joined result-set is obtained as −

    ID NAME AMOUNT DATE
    1 Ramesh NULL NULL
    2 Khilan 1560.00 2009-11-20 00:00:00
    3 Kaushik 1500.00 2009-10-08 00:00:00
    3 Kaushik 3000.00 2009-10-08 00:00:00
    4 Chaitali 2060.00 2008-05-20 00:00:00
    5 Hardik NULL NULL
    6 Komal NULL NULL
    7 Muffy NULL NULL

    Joining Multiple Tables with Left Join

    Left Join also joins multiple tables where the first table is returned as a whole and the next tables are matched with the rows in the first table. If the records are not matched, NULL is returned.

    Syntax

    The syntax to join multiple tables using Left Join is given below −

    SELECT column1, column2, column3...
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name
LEFT JOIN table3
ON table2.column_name = table3.column_name
.
.
.

    Example

    To demonstrate Left Join with multiple tables, let us consider the previously created tables CUSTOMERS and ORDERS. In addition to these we will create another table named EMPLOYEE, which consists of the details of employees in an organization and sales made by them, using the following query −

    CREATE TABLE EMPLOYEE (
   EID INT NOT NULL,
   EMPLOYEE_NAME VARCHAR (30) NOT NULL,
   SALES_MADE DECIMAL (20)
);

    Now, we can insert values into this empty tables using the INSERT statement as follows −

    INSERT INTO EMPLOYEE VALUES
(102, ''SARIKA'', 4500),
(100, ''ALEKHYA'', 3623),
(101, ''REVATHI'', 1291),
(103, ''VIVEK'', 3426);

    The table is created as −

    EID EMPLOYEE_NAME SALES_MADE
    102 SARIKA 4500
    100 ALEKHYA 3623
    101 REVATHI 1291
    103 VIVEK 3426

    Left Join Query:

    Let us join these three tables using the left join query given below −

    SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
LEFT JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;

    Output

    The resultant table is obtained as follows −

    ID NAME DATE EMPLOYEE_NAME
    1 Ramesh NULL NULL
    2 Khilan 2009-11-20 00:00:00 REVATHI
    3 Kaushik 2009-10-08 00:00:00 ALEKHYA
    3 Kaushik 2009-10-08 00:00:00 SARIKA
    4 Chaitali 2008-05-20 00:00:00 VIVEK
    5 Hardik NULL NULL
    6 Komal NULL NULL
    7 Muffy NULL NULL

    Left Join with WHERE Clause

    To filter the records after joining two tables, a WHERE clause can be applied.

    Syntax

    The syntax of Left Join when used with WHERE clause is given below −

    SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition

    Example

    Records in the combined database tables can be filtered using the WHERE clause. Consider the previous two tables CUSTOMERS and ORDERS; and join them using the left join query by applying some constraints using the WHERE clause.

    SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

    Output

    The output is obtained as − −

    ID NAME DATE AMOUNT
    3 Kaushik 2009-10-08 00:00:00 3000.00
    4 Chaitali 2008-05-20 00:00:00 2060.00

    Left Join Using a Client Program

    We can also perform the left join operation on one or more tables using a client program.

    Syntax

    To join two tables using left join through a PHP program, we need to execute the SQL query with LEFT JOIN clause using the mysqli function query() as follows −

    $sql = ''SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author
$mysqli->query($sql);

    To join two tables using left join through a JavaScript program, we need to execute the SQL query with LEFT JOIN clause using the query() function of mysql2 library as follows −

    sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author";
con.query(sql);

    To join two tables using left join through a Java program, we need to execute the SQL query with LEFT JOIN clause using the JDBC function executeQuery() as follows −

    String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author";
st.executeQuery(sql);

    To join two tables using left join through a python program, we need to execute the SQL query with LEFT JOIN clause using the execute() function of the MySQL Connector/Python as follows −

    left_join_query = "SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUST_ID"
cursorObj.execute(left_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 LEFT JOIN tcount_tbl b
			ON a.tutorial_author = b.tutorial_author
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    echo " following is the both table details after executing left join! n";
    while ($row = $result->fetch_assoc()) {
        printf( "Id: %s, Author: %s, Count: %d", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"] );
        printf("n");
    }
} else {
    printf(''No record found.
    '');
}
mysqli_free_result($result);
$mysqli->close();

    Output

    The output obtained is as follows −

    following is the both table details after executing left join!
Id: 1, Author: John Poul, Count: 0
Id: 2, Author: Abdul S, Count: 0
Id: 3, Author: Sanjay, Count: 1
Id: 101, Author: Aman kumar, Count: 0
Id: 102, Author: Sarika Singh, Count: 0

    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);
  //left join
  sql =
    "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT 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 },
   { tutorial_id: 2, tutorial_author: ''Abdul S'', tutorial_count: null },
   {
     tutorial_id: 101,
     tutorial_author: ''Aman kumar'',
     tutorial_count: null
   },
   {
     tutorial_id: 102,
     tutorial_author: ''Sarika Singh'',
     tutorial_count: null
   }
]

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

public class LeftJoin {
   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 LEFT JOIN...!;
         String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a LEFT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
         ResultSet resultSet = statement.executeQuery(sql);
         System.out.println("Table records after LEFT 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 LEFT Join...!
1 John Paul 1
2 Abdul S null
3 Sanjay 1
4 Sasha Lee null
5 Chris Welsh null

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

    (1, ''Ramesh'', None, None)
(2, ''Khilan'', 1560, ''2009-11-20 00:00:00'')
(3, ''Kaushik'', 1500, ''2009-10-08 00:00:00'')
(3, ''Kaushik'', 3000, ''2009-10-08 00:00:00'')
(4, ''Chaital'', 2060, ''2008-05-20 00:00:00'')
(5, ''Hardik'', None, None)
(6, ''Komal'', None, None)
(7, ''Muffy'', None, None)

    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

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

    MySQL – Cross Join

    Table of content

    MySQL Cross Join

    A MySQL Cross Join combines each row of the first table with each row of second table. It is a basic type of inner join that is used to retrieve the Cartesian product (or cross product) of two individual tables (i.e. permutations).

    A Cartesian product, or a cross product, is the result achieved from multiplication of two sets. This is done by multiplying all the possible pairs from both the sets.

    The sample figure below illustrates the cross join in a simple manner.

    Cross Join

    Syntax

    Following is the basic syntax of the Cross Join query in MySQL −

    SELECT column_name(s)
FROM table1
CROSS JOIN table2

    Example

    In this example of cross join, let us first create a table named CUSTOMERS, which contains personal details of customers including their name, age, address and salary etc.

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

    Now insert values into this table using the INSERT statement as follows −

    INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 );

    The table will be created as −

    ID NAME AGE ADDRESS SALARY
    1 Ramesh 32 Ahmedabad 2000.00
    2 Khilan 25 Delhi 1500.00

    Let us create another table ORDERS, containing the details of orders made and the date they are made on.

    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 this table as follows −

    INSERT INTO ORDERS VALUES
(100, ''2009-10-08 00:00:00'', 3, 1500.00),
(101, ''2009-11-20 00:00:00'', 2, 1560.00);

    The table is displayed as follows −

    OID DATE CUSTOMER_ID AMOUNT
    100 2009-10-08 00:00:00 3 1500.00
    101 2009-11-20 00:00:00 2 1560.00

    Now, if we execute the following Cross Join query on these two tables given above, the cross join combines each row in CUSTOMERS table with each row in ORDERS table.

    SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
CROSS JOIN ORDERS;

    Output

    The resultant table is as follows −

    ID NAME AMOUNT DATE
    2 Khilan 1500.00 2009-10-08 00:00:00
    1 Ramesh 1560 2009-11-20 00:00:00
    2 Khilan 1560 2009-11-20 00:00:00
    1 Ramesh 1500.00 2009-10-08 00:00:00

    Joining Multiple Tables with Cross Join

    We can also join more than two tables using cross join. In this case, multiple-way permutations are displayed and the resultant table is expected to contain way more records than the individual tables.

    Syntax

    Following is the syntax to join multiple tables using cross join in MySQL −

    SELECT column_name(s)
FROM table1
CROSS JOIN table2
CROSS JOIN table3
CROSS JOIN table4
.
.
.

    Example

    Let us now combine three tables CUSTOMERS, ORDERS and ORDER_RANGE, to demonstrate combining multiple tables using cross join.

    We will create the ORDER_RANGE table using the query below −

    CREATE TABLE ORDER_RANGE (
   SNO INT NOT NULL,
   ORDER_RANGE VARCHAR (20) NOT NULL,
);

    Now, we can insert values into this empty tables using the INSERT statement as follows −

    INSERT INTO ORDER_RANGE VALUES
(1, ''1-100''),
(2, ''100-200''),
(3, ''200-300'');

    The ORDER_RANGE table is as follows −

    SNO ORDER_RANGE
    1 1-100
    2 100-200
    3 200-300

    Now we use the following cross join query on the given tables,

    SELECT ID, NAME, AMOUNT, DATE, ORDER_RANGE
FROM CUSTOMERS
CROSS JOIN ORDERS
CROSS JOIN ORDER_RANGE;

    Output

    The resultant table is given below −

    ID NAME AMOUNT DATE ORDER_RANGE
    2 Khilan 1560 2009-11-20 00:00:00 1-100
    1 Ramesh 1560 2009-11-20 00:00:00 1-100
    2 Khilan 1500.00 2009-10-08 00:00:00 1-100
    1 Ramesh 1500.00 2009-10-08 00:00:00 1-100
    2 Khilan 1560 2009-11-20 00:00:00 100-200
    1 Ramesh 1560 2009-11-20 00:00:00 100-200
    2 Khilan 1500.00 2009-10-08 00:00:00 100-200
    1 Ramesh 1500.00 2009-10-08 00:00:00 100-200
    2 Khilan 1560 2009-11-20 00:00:00 200-300
    1 Ramesh 1560 2009-11-20 00:00:00 200-300
    2 Khilan 1500.00 2009-10-08 00:00:00 200-300
    1 Ramesh 1500.00 2009-10-08 00:00:00 200-300

    Cross Join Using Client Program

    We can also perform the Cross join operation on one or more tables using a client program.

    Syntax

    To perform cross Join through a PHP program, we need to execute the SQL query with CROSS JOIN clause using the mysqli function query() as follows −

    $sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b";
$mysqli->query($sql);

    To perform cross Join through a JavaScript program, we need to execute the SQL query with CROSS JOIN clause using the query() function of mysql2 library as follows −

    sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b";
con.query(sql);

    To perform cross Join through a Java program, we need to execute the SQL query with CROSS JOIN clause using the JDBC function executeQuery() as follows −

    String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author";
statement.executeQuery(sql);

    To perform cross Join through a Python program, we need to execute the SQL query with CROSS JOIN clause using the execute() function of the MySQL Connector/Python as follows −

    cross_join_query = "SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS CROSS JOIN ORDERS"
cursorObj.execute(cross_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 CROSS JOIN tcount_tbl b
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    echo " following is the details after executing cross join! n";
    while ($row = $result->fetch_assoc()) {
        printf("Id: %s, Author: %s, Count: %d", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"]);
        printf("n");
    }
} else {
    printf(''No record found.
    '');
}
mysqli_free_result($result);
$mysqli->close();

    Output

    The output obtained is as follows −

    following is the details after executing cross join!
Id: 102, Author: Sarika Singh, Count: 20
Id: 101, Author: Aman kumar, Count: 20
Id: 3, Author: Sanjay, Count: 20
Id: 2, Author: Abdul S, Count: 20
Id: 1, Author: John Poul, Count: 20
Id: 102, Author: Sarika Singh, Count: 5
Id: 101, Author: Aman kumar, Count: 5
Id: 3, Author: Sanjay, Count: 5
Id: 2, Author: Abdul S, Count: 5
Id: 1, Author: John Poul, Count: 5
Id: 102, Author: Sarika Singh, Count: 4
Id: 101, Author: Aman kumar, Count: 4
Id: 3, Author: Sanjay, Count: 4
Id: 2, Author: Abdul S, Count: 4
Id: 1, Author: John Poul, Count: 4
Id: 102, Author: Sarika Singh, Count: 20
Id: 101, Author: Aman kumar, Count: 20
Id: 3, Author: Sanjay, Count: 20
Id: 2, Author: Abdul S, Count: 20
Id: 1, Author: John Poul, Count: 20
Id: 102, Author: Sarika Singh, Count: 1
Id: 101, Author: Aman kumar, Count: 1
Id: 3, Author: Sanjay, Count: 1
Id: 2, Author: Abdul S, Count: 1
Id: 1, Author: John Poul, Count: 1
Id: 102, Author: Sarika Singh, Count: 1
Id: 101, Author: Aman kumar, Count: 1
Id: 3, Author: Sanjay, Count: 1
Id: 2, Author: Abdul S, Count: 1
Id: 1, Author: John Poul, 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);

  //Cross Join
  sql =
    "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

    Output

    The output produced is as follows −

    [
  {
    tutorial_id: 102,
    tutorial_author: ''Sarika Singh'',
    tutorial_count: 20
  },
  {
    tutorial_id: 101,
    tutorial_author: ''Aman kumar'',
    tutorial_count: 20
  },
  { tutorial_id: 2, tutorial_author: ''Abdul S'', tutorial_count: 20 },
  { tutorial_id: 1, tutorial_author: ''John Poul'', tutorial_count: 20 },
  {
    tutorial_id: 102,
    tutorial_author: ''Sarika Singh'',
    tutorial_count: 5
  },
  {
    tutorial_id: 101,
    tutorial_author: ''Aman kumar'',
    tutorial_count: 5
  },
  { tutorial_id: 2, tutorial_author: ''Abdul S'', tutorial_count: 5 },
  { tutorial_id: 1, tutorial_author: ''John Poul'', tutorial_count: 5 },
  {
    tutorial_id: 102,
    tutorial_author: ''Sarika Singh'',
    tutorial_count: 4
  },
  {
    tutorial_id: 101,
    tutorial_author: ''Aman kumar'',
    tutorial_count: 4
  },
  { tutorial_id: 2, tutorial_author: ''Abdul S'', tutorial_count: 4 },
  { tutorial_id: 1, tutorial_author: ''John Poul'', tutorial_count: 4 },
  {
    tutorial_id: 102,
    tutorial_author: ''Sarika Singh'',
    tutorial_count: 20
  },
  {
    tutorial_id: 101,
    tutorial_author: ''Aman kumar'',
    tutorial_count: 20
  },
  { tutorial_id: 2, tutorial_author: ''Abdul S'', tutorial_count: 20 },
  { tutorial_id: 1, tutorial_author: ''John Poul'', tutorial_count: 20 },
  {
    tutorial_id: 102,
    tutorial_author: ''Sarika Singh'',
    tutorial_count: 1
  },
  {
    tutorial_id: 101,
    tutorial_author: ''Aman kumar'',
    tutorial_count: 1
  },
  { tutorial_id: 2, tutorial_author: ''Abdul S'', tutorial_count: 1 },
  { tutorial_id: 1, tutorial_author: ''John Poul'', tutorial_count: 1 },
  {
    tutorial_id: 102,
    tutorial_author: ''Sarika Singh'',
    tutorial_count: 1
  },
  {
    tutorial_id: 101,
    tutorial_author: ''Aman kumar'',
    tutorial_count: 1
  },
  { tutorial_id: 2, tutorial_author: ''Abdul S'', tutorial_count: 1 },
  { tutorial_id: 1, tutorial_author: ''John Poul'', tutorial_count: 1 },
  {
    tutorial_id: 102,
    tutorial_author: ''Sarika Singh'',
    tutorial_count: 2
  },
  {
    tutorial_id: 101,
    tutorial_author: ''Aman kumar'',
    tutorial_count: 2
  },
  { tutorial_id: 2, tutorial_author: ''Abdul S'', tutorial_count: 2 },
  { 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 CrossJoin {
   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 CROSS JOIN...!;
         String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a CROSS JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
         ResultSet resultSet = statement.executeQuery(sql);
         System.out.println("Table records after CROSS 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 CROSS 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()
cross_join_query = f"""SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS CROSS JOIN ORDERS"""
cursorObj.execute(cross_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 −

    (1, ''Ramesh'', 2060, ''2008-05-20 00:00:00'')
(1, ''Ramesh'', 1560, ''2009-11-20 00:00:00'')
(1, ''Ramesh'', 1500, ''2009-10-08 00:00:00'')
(1, ''Ramesh'', 3000, ''2009-10-08 00:00:00'')
(2, ''Khilan'', 2060, ''2008-05-20 00:00:00'')
(2, ''Khilan'', 1560, ''2009-11-20 00:00:00'')
(2, ''Khilan'', 1500, ''2009-10-08 00:00:00'')
(2, ''Khilan'', 3000, ''2009-10-08 00:00:00'')
(3, ''kaushik'', 2060, ''2008-05-20 00:00:00'')
(3, ''kaushik'', 1560, ''2009-11-20 00:00:00'')
(3, ''kaushik'', 1500, ''2009-10-08 00:00:00'')
(3, ''kaushik'', 3000, ''2009-10-08 00:00:00'')
(4, ''Chaital'', 2060, ''2008-05-20 00:00:00'')
(4, ''Chaital'', 1560, ''2009-11-20 00:00:00'')
(4, ''Chaital'', 1500, ''2009-10-08 00:00:00'')
(4, ''Chaital'', 3000, ''2009-10-08 00:00:00'')
(5, ''Hardik'', 2060, ''2008-05-20 00:00:00'')
(5, ''Hardik'', 1560, ''2009-11-20 00:00:00'')
(5, ''Hardik'', 1500, ''2009-10-08 00:00:00'')
(5, ''Hardik'', 3000, ''2009-10-08 00:00:00'')
(6, ''Komal'', 2060, ''2008-05-20 00:00:00'')
(6, ''Komal'', 1560, ''2009-11-20 00:00:00'')
(6, ''Komal'', 1500, ''2009-10-08 00:00:00'')
(6, ''Komal'', 3000, ''2009-10-08 00:00:00'')
(7, ''Muffy'', 2060, ''2008-05-20 00:00:00'')
(7, ''Muffy'', 1560, ''2009-11-20 00:00:00'')
(7, ''Muffy'', 1500, ''2009-10-08 00:00:00'')
(7, ''Muffy'', 3000, ''2009-10-08 00:00: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

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

    MySQL – Right Join

    Table of content

    MySQL Right Join

    The Right Join or Right Outer Join query in MySQL returns all rows from the right table, even if there are no matches in the left table. So, if zero records are matched in the left table, the right join will still return a row in the result, but with a NULL value in each column of the left table.

    In short, a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

    Right Join

    The resultant table displayed after implementing the Right Join is not stored anywhere in the database.

    Syntax

    Following is the basic syntax of Right Join in SQL −

    SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

    Example

    Assume we are creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

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

    Now insert values into this table using the INSERT statement as follows −

    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

    Let us create another table ORDERS, containing the details of orders made and the date they are made on.

    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 this 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

    Right join Query

    Now, let us join these two tables using the Right Join query as follows.

    SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

    Output

    This would produce the following result −

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

    Joining Multiple Tables with Right Join

    Like Left Join, Right Join also joins multiple tables. However, the contrast occurs where the second table is returned as a whole instead of the first.

    Syntax

    Following is the syntax to join multiple tables using Right Join −

    SELECT column1, column2, column3...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name
RIGHT JOIN table3
ON table2.column_name = table3.column_name
.
.
.

    Example

    Here, let us consider the previously created tables CUSTOMERS and ORDERS; along with the newly created table EMPLOYEE.

    We will create the EMPLOYEE table using the query below −

    CREATE TABLE EMPLOYEE (
   EID INT NOT NULL,
   EMPLOYEE_NAME VARCHAR (30) NOT NULL,
   SALES_MADE DECIMAL (20)
);

    Now, we can insert values into this empty tables using the INSERT statement as follows −

    INSERT INTO EMPLOYEE VALUES
(102, ''SARIKA'', 4500),
(100, ''ALEKHYA'', 3623),
(101, ''REVATHI'', 1291),
(103, ''VIVEK'', 3426);

    The table is created as −

    EID EMPLOYEE_NAME SALES_MADE
    102 SARIKA 4500
    100 ALEKHYA 3623
    101 REVATHI 1291
    103 VIVEK 3426

    Let us join these three tables using the Right Join query given below −

    SELECT CUSTOMERS.ID, CUSTOMERS.NAME, ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
RIGHT JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;

    Through this query, we are trying to display the records of Customer IDs, Customer names, Orders made on specific dates and names of the employees that sold them.

    Output

    The resultant table is obtained as follows −

    ID NAME DATE EMPLOYEE_NAME
    3 Kaushik 2009-10-08 00:00:00 SARIKA
    3 Kaushik 2009-10-08 00:00:00 ALEKHYA
    2 Khilan 2009-11-20 00:00:00 REVATHI
    4 Chaitali 2008-05-20 00:00:00 VIVEK

    Right Join with WHERE Clause

    A WHERE Clause is used to filter out records that satisfy the condition specified by it. This clause can be used with the Right Join technique to apply constraints on the result-set obtained.

    Syntax

    The syntax of Right Join when used with WHERE clause is given below −

    SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition

    Example

    Records in the combined database tables can be filtered using the WHERE clause. Consider the previous two tables CUSTOMERS and ORDERS; and join them using the following query −

    SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 1000.00;

    Output

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

    Right Join Using a Client Program

    We can also perform the Right join operation on one or more tables using a client program.

    Syntax

    To join two tables using right join through a PHP program, we need to execute the SQL query with RIGHT JOIN clause using the mysqli function query() as follows −

    $sql = ''SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a RIGHT JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author
$mysqli->query($sql);

    To join two tables using right join through a JavaScript program, we need to execute the SQL query with RIGHT JOIN clause using the query() function of mysql2 library as follows −

    sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a RIGHT JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author";
con.query(sql);

    To join two tables using right join through a Java program, we need to execute the SQL query with RIGHT JOIN clause using the JDBC function executeQuery() as follows −

    String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a RIGHT JOIN tcount_tbl b
ON a.tutorial_author = b.tutorial_author";
st.executeQuery(sql);

    To join two tables using right join through a python program, we need to execute the SQL query with RIGHT JOIN clause using the execute() function of the MySQL Connector/Python as follows −

    right_join_query = "SELECT ID, NAME, AMOUNT, DATE FROM CUSTOMERS RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUST_ID"
cursorObj.execute(right_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 RIGHT JOIN tcount_tbl b
        ON a.tutorial_author = b.tutorial_author
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    echo " following is the both table details after executing right join! n";
    while ($row = $result->fetch_assoc()) {
        printf( "Id: %s, Author: %s, Count: %d", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"] );
        printf("n");
    }
} else {
    printf(''No record found.
    '');
}
mysqli_free_result($result);
$mysqli->close();

    Output

    The output obtained is as follows −

    following is the both table details after executing right join!
Id: , Author: , Count: 20
Id: , Author: , Count: 5
Id: , Author: , Count: 4
Id: , Author: , Count: 20
Id: , Author: , Count: 1
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);

  //Right Join
  sql =
    "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a RIGHT 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: null, tutorial_author: null, tutorial_count: 20 },
   { tutorial_id: null, tutorial_author: null, tutorial_count: 5 },
   { tutorial_id: null, tutorial_author: null, tutorial_count: 4 },
   { tutorial_id: null, tutorial_author: null, tutorial_count: 20 },
   { tutorial_id: null, tutorial_author: null, tutorial_count: 1 },
   { tutorial_id: null, tutorial_author: null, tutorial_count: 1 },
   { 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 RightJoin {
   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 RIGHT JOIN...!;
         String sql = "SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a RIGHT JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author";
         ResultSet resultSet = statement.executeQuery(sql);
         System.out.println("Table records after LEFT 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 LEFT Join...!
null null 20
null null 5
null null 4
null null 20
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()
right_join_query = f"""
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUST_ID
"""
cursorObj.execute(right_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'', 3000, ''2009-10-08 00:00:00'')
(3, ''kaushik'', 1500, ''2009-10-08 00:00:00'')
(2, ''Khilan'', 1560, ''2009-11-20 00:00:00'')
(4, ''Chaital'', 2060, ''2008-05-20 00:00: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

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

    MySQL – Self Join

    Table of content

    MySQL Self Join

    The MySQL Self Join is used to join a table to itself as if the table were two tables. To carry this out, at least one table is temporarily renamed in the MySQL statement.

    Self Join is a type of inner join, which performed in cases where the comparison between two columns of a same table is required; probably to establish a relationship between them. In other words, a table is joined with itself when it contains both Foreign Key and Primary Key in it.

    However, unlike queries of other joins, we use WHERE clause to specify the condition for the table to combine with itself; instead of the ON clause.

    Syntax

    Following is the basic syntax of Self Join in MySQL −

    SELECT column_name(s)
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

    Here, the WHERE clause could be any given expression based on your requirement.

    Example

    Self Join only requires one table to join itself; so, let us create a CUSTOMERS table containing the customer details like their names, age, address and the salary they earn.

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

    Now insert values into this table using the INSERT statement as follows −

    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 join this table using the following Self Join query. Our aim is to establish a relationship among the said customers on the basis of their earnings. We are doing this with the help of WHERE clause.

    SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS,
a.SALARY as LOWER_SALARY FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;

    Output

    The resultant table displayed will list out all the customers that earn lesser than other customers −

    ID EARNS_HIGHER EARNS_LESS LOWER_SALARY
    2 Ramesh Khilan 1500.00
    2 Kaushik Khilan 1500.00
    6 Chaitali Komal 4500.00
    3 Chaitali Kaushik 2000.00
    2 Chaitali Khilan 1500.00
    1 Chaitali Ramesh 2000.00
    6 Hardik Komal 4500.00
    4 Hardik Chaitali 6500.00
    3 Hardik Kaushik 2000.00
    2 Hardik Khilan 1500.00
    1 Hardik Ramesh 2000.00
    3 Komal Kaushik 2000.00
    2 Komal Khilan 1500.00
    1 Komal Ramesh 2000.00
    6 Muffy Komal 4500.00
    5 Muffy Hardik 8500.00
    4 Muffy Chaitali 6500.00
    3 Muffy Kaushik 2000.00
    2 Muffy Khilan 1500.00
    1 Muffy Ramesh 2000.00

    Self Join with ORDER BY Clause

    Furthermore, after joining a table with itself using self join, the records in the combined table can also be sorted in an ascending order using the ORDER BY clause. Following is the syntax for it −

    SELECT column_name(s)
FROM table1 a, table1 b
WHERE a.common_field = b.common_field
ORDER BY column_name;

    Example

    In this example, executing the query below will join the CUSTOMERS table with itself using self join on a WHERE clause. Then, arrange the records in an ascending order using the ORDER BY clause with respect to a specified column. Here, we are arranging the records based on the salary column

    SELECT  a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS,
a.SALARY as LOWER_SALARY FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY ORDER BY a.SALARY;

    Output

    The resultant table is displayed as follows −

    ID EARNS_HIGHER EARNS_LESS LOWER_SALARY
    2 Ramesh Khilan 1500.00
    2 Kaushik Khilan 1500.00
    2 Chaitali Khilan 1500.00
    2 Hardik Khilan 1500.00
    2 Komal Khilan 1500.00
    2 Muffy Khilan 1500.00
    3 Chaitali Kaushik 2000.00
    1 Chaitali Ramesh 2000.00
    3 Hardik Kaushik 2000.00
    1 Hardik Ramesh 2000.00
    3 Komal Kaushik 2000.00
    1 Komal Ramesh 2000.00
    3 Muffy Kaushik 2000.00
    1 Muffy Ramesh 2000.00
    6 Chaitali Komal 4500.00
    6 Hardik Komal 4500.00
    6 Muffy Komal 4500.00
    4 Hardik Chaitali 6500.00
    4 Muffy Chaitali 6500.00
    5 Muffy Hardik 8500.00

    Self Join Using Client Program

    We can also perform the Self join operation on one or more tables using a client program.

    Syntax

    To perform Self Join through a PHP program, we need to execute the SQL query using the mysqli function query() as follows −

    $sql = ''SELECT a.tutorial_id, a.tutorial_title, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a, tcount_tbl b
WHERE a.tutorial_author = b.tutorial_author
$mysqli->query($sql);

    To perform Self Join through a JavaScript program, we need to execute the SQL query using the query() function of mysql2 library as follows −

    sql = "SELECT a.tutorial_id, a.tutorial_title, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a, tcount_tbl b
WHERE a.tutorial_author = b.tutorial_author";
con.query(sql);

    To perform Self Join through a Java program, we need to execute the SQL query using the JDBC function executeQuery() as follows −

    String sql = "SELECT a.tutorial_id, a.tutorial_title, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a, tcount_tbl b
WHERE a.tutorial_author = b.tutorial_author";
statement.executeQuery(sql);

    To perform Self Join through a python program, we need to execute the SQL query using the execute() function of the MySQL Connector/Python as follows −

    self_join_query = "SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS, a.SALARY as LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b WHERE a.SALARY

    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_title, a.tutorial_author, b.tutorial_count
FROM tutorials_tbl a, tcount_tbl b WHERE a.tutorial_author = b.tutorial_author
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
    echo " following is the details after executing SELF join! n";
    while ($row = $result->fetch_assoc()) {
        printf("Id: %s, Title: %s, Author: %s, Count: %d", $row["tutorial_id"], $row["tutorial_title"], $row["tutorial_author"], $row["tutorial_count"]);
        printf("n");
    }
} else {
    printf(''No record found.
    '');
}
mysqli_free_result($result);
$mysqli->close();

    Output

    The output obtained is as follows −

    following is the details after executing SELF join!
Id: 3, Title: JAVA Tutorial, 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);

  //Self Join
  sql = "SELECT a.tutorial_id, a.tutorial_title, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a, tcount_tbl b
  WHERE 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_title: ''Learn PHP'',
    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 SelfJoin {
   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 Self JOIN...!;
         String sql = "SELECT a.tutorial_id, a.tutorial_title, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a, tcount_tbl b
         WHERE a.tutorial_author = b.tutorial_author";
         ResultSet resultSet = statement.executeQuery(sql);
         System.out.println("Table records after Self 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 Self Join...!
1 Learn PHP John Paul
3 JAVA Tutorial Sanjay

    import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
self_join_query = f"""SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS, a.SALARY as LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b WHERE a.SALARY

    Output
    

    Following is the output of the above code −
    

    (4, ''Ramesh'', ''Chaital'', Decimal(''1200.00''))
(6, ''Khilan'', ''Komal'', Decimal(''7000.00''))
(4, ''Khilan'', ''Chaital'', Decimal(''1200.00''))
(1, ''Khilan'', ''Ramesh'', Decimal(''4000.00''))
(7, ''kaushik'', ''Muffy'', Decimal(''10000.00''))
(6, ''kaushik'', ''Komal'', Decimal(''7000.00''))
(5, ''kaushik'', ''Hardik'', Decimal(''10000.00''))
(4, ''kaushik'', ''Chaital'', Decimal(''1200.00''))
(2, ''kaushik'', ''Khilan'', Decimal(''8000.00''))
(1, ''kaushik'', ''Ramesh'', Decimal(''4000.00''))
(6, ''Hardik'', ''Komal'', Decimal(''7000.00''))
(4, ''Hardik'', ''Chaital'', Decimal(''1200.00''))
(2, ''Hardik'', ''Khilan'', Decimal(''8000.00''))
(1, ''Hardik'', ''Ramesh'', Decimal(''4000.00''))
(4, ''Komal'', ''Chaital'', Decimal(''1200.00''))
(1, ''Komal'', ''Ramesh'', Decimal(''4000.00''))
(6, ''Muffy'', ''Komal'', Decimal(''7000.00''))
(4, ''Muffy'', ''Chaital'', Decimal(''1200.00''))
(2, ''Muffy'', ''Khilan'', Decimal(''8000.00''))
(1, ''Muffy'', ''Ramesh'', Decimal(''4000.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

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

    MySQL – Full Join

    Table of content

    MySQL Full Join creates a new table by joining two tables as a whole. The joined table contains all records from both the tables and fill in NULLs for missing matches on either side. In short, full join is a type of outer join that combines the results of both left and right joins.

    MySQL Full Join

    In MySQL, there is no provision to perform full join operation. We can, however, imitate this operation to produce the same results.

    The result-set obtained from performing full join is a union of result-sets obtained from left join and right join. Thus, we can first retrieve result-sets from left and right join operations and combine them using the UNION keyword.

    But, this method only works for cases where duplicate records are non-existent. If we want to include the duplicate rows, using UNION ALL keyword to combine the result-sets is preferred.

    Syntax

    Following is the basic syntax to emulate Full Join −

    SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field

[UNION | UNION ALL]

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

    Example

    In this example, we are imitating the full join operation using UNION or UNION ALL keyword. First, 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)
);

    Now insert values into this table using the INSERT statement 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

    Let us create another table ORDERS, containing the details of orders made and the date they are made on.

    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 this 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

    Full Join Query −

    On executing the following query, we will produce the union of two tables CUSTOMERS and ORDERS.

    SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

    Output

    The resultant table is produced as follows −

    ID NAME AMOUNT DATE
    1 Ramesh NULL NULL
    2 Khilan 1560 2009-11-20 00:00:00
    3 Kaushik 3000 2009-10-08 00:00:00
    3 Kaushik 1500 2009-10-08 00:00:00
    4 Chaitali 2060 2008-05-20 00:00:00
    5 Hardik NULL NULL
    6 Komal NULL NULL
    7 Muffy NULL NULL

    Full Join with WHERE Clause

    With Joins, we are filtering records using the ON clause, by default. Let us suppose there is a further requirement to filter records based on a certain condition, we can make use of WHERE clause with the Joins.

    Syntax

    The syntax of Full Join when used with WHERE clause is given below −

    SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name = table_name2.column_name
WHERE condition

    Example

    Consider the previous two tables CUSTOMERS and ORDERS, and join them using the following Full Join query by applying some constraints using the WHERE clause.

    SELECT ID, NAME, DATE, AMOUNT
FROM CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00

UNION

SELECT ID, NAME, DATE, AMOUNT
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

    Output

    The resultant table after applying the where clause with full join contains the rows that has amount values greater than 2000.00 −

    ID NAME DATE AMOUNT
    3 Kaushik 2009-10-08 00:00:00 3000.00
    4 Chaitali 2008-05-20 00:00:00 2060.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

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

    MySQL – Update Join

    Table of content

    To update the data entered in a single database table using MySQL, you can use the UPDATE statement. However, to update the data in multiple database tables, we use the UPDATE… JOIN statement.

    MySQL UPDATE… JOIN

    Usually, JOINS in MySQL are used to fetch the combination of rows from multiple tables, with respect to a matching field. And since the UPDATE statement only modifies the data in a single table, we combine multiple tables into one using JOINS and then update them. This is also known as cross-table modification.

    Syntax

    Following is the basic syntax of the UPDATE… JOIN statement −

    UPDATE table(s)
SET column1 = value1, column2 = value2, ...
FROM table1
JOIN table2 ON column3 = column4;

    Example

    Let us first create a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

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

    Now insert values into this table using the INSERT statement as follows −

    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 CUSTOMERS 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

    Let us create another table ORDERS, containing the details of orders made and the date they are made on.

    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 this 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 ORDERS 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

    Use the following UPDATE… JOIN query to cross-modify multiple tables (CUSTOMERS and ORDERS) −

    UPDATE CUSTOMERS
JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000;

    Verification

    As we can see in the CUSTOMERS table below, the changes we have performed in the above query are reflected −

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

    UPDATE… JOIN with WHERE Clause

    The ON clause in UPDATE… JOIN query is used to apply constraints on the records to be updated. In addition to it, we can also use WHERE clause to make the constraints stricter. The syntax of it is as follows −

    UPDATE table(s)
SET column1 = value1, column2 = value2, ...
FROM table1
JOIN table2 ON column3 = column4
WHERE condition;

    Example

    Observe the query below. Here, we are trying to increase the salary of CUSTOMERS who only earn 2000.00 −

    UPDATE CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
SET SALARY = SALARY + 1000
WHERE CUSTOMERS.SALARY = 2000.00;

    Verification

    As we can see in the CUSTOMERS table below, the CUSTOMERS who are earning the salary of 2000 got an increment of 1000 −

    ID NAME AGE ADDRESS SALARY
    1 Ramesh 32 Ahmedabad 3000.00
    2 Khilan 25 Delhi 1500.00
    3 Kaushik 23 Kota 3000.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

    Update Join Using Client Program

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

    Syntax

    To perform Update Join through a PHP program, we need to execute the UPDATE statement with JOIN clause using the mysqli function query() as follows −

    $sql = ''UPDATE tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author
SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100
$mysqli->query($sql);

    To perform Update Join through a JavaScript program, we need to execute the UPDATE statement with JOIN clause using the query() function of mysql2 library as follows −

    sql = "UPDATE Customers c JOIN Orders o ON c.ID = o.CUSTOMER_ID  SET c.SALARY = c.SALARY + o.AMOUNT";
con.query(sql);

    To perform Update Join through a Java program, we need to execute the UPDATE statement with JOIN clause using the JDBC function executeUpdate() as follows −

    String sql = "UPDATE tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author
SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100";
statement.executeUpdate(sql);

    To perform Update Join through a Python program, we need to execute the UPDATE statement with JOIN clause using the execute() function of the MySQL Connector/Python as follows −

    update_join_query = "UPDATE Customers c JOIN Orders o ON c.ID = o.CUST_ID SET c.SALARY = c.SALARY + o.AMOUNT"
cursorObj.execute(update_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();
}
// Updating Join
$sql = ''UPDATE tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100
if ($mysqli->query($sql)) {
    echo "Join updated successfully! n";
} else {
    echo "Join could not be updated! n";
}

// Selecting the updated value
$update_res = ''SELECT a.tutorial_author, b.tutorial_count FROM tutorials_tbl a INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author

$result = $mysqli->query($update_res);

if ($result->num_rows > 0) {
    echo "Updated tutorial_count! n";
    while ($row = $result->fetch_assoc()) {
        printf("Author: %s, Count: %d", $row["tutorial_author"], $row["tutorial_count"]);
        printf("
    ");
    }
} else {
    printf(''No record found.
    '');
}
mysqli_free_result($result);
$mysqli->close();

    Output

    The output obtained is as follows −

    Join updated successfully!
Updated tutorial_count!
Author: John Paul, Count: 101
Author: Sanjay, Count: 101

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

  //UPDATE JOIN
  sql = "UPDATE Customers c JOIN Orders o ON c.ID = o.CUSTOMER_ID  SET c.SALARY = c.SALARY + o.AMOUNT";

  //displaying the updated data along with ID;
  sql =
    "SELECT c.ID, c.SALARY FROM Customers c INNER JOIN Orders o  ON c.ID = o.CUSTOMER_ID";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

    Output

    The output produced is as follows −

    [
  { ID: 3, SALARY: ''5000.00'' },
  { ID: 3, SALARY: ''5000.00'' },
  { ID: 2, SALARY: ''3060.00'' },
  { ID: 4, SALARY: ''8560.00'' }
]

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

public class UpdateJoin {
   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 Update JOIN...!;
         String sql = "UPDATE tcount_tbln" +
         "INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_authorn" +
         "SET tcount_tbl.tutorial_count = tcount_tbl.tutorial_count + 100n";
         statement.executeUpdate(sql);
         System.out.println("tutorial_count updated successfully...!");

         //fetch the records...!;
         ResultSet resultSet = statement.executeQuery("SELECT a.tutorial_author, b.tutorial_count FROM tutorials_tbl a INNER JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getInt(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

    Output

    The output obtained is as shown below −

    Connected successfully...!
tutorial_count updated successfully...!
John Paul 201
Sanjay 201

    import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
update_join_query = f"""UPDATE Customers c JOIN Orders o ON c.ID = o.CUST_ID SET c.SALARY = c.SALARY + o.AMOUNT"""
cursorObj.execute(update_join_query)
connection.commit()
print("updated successfully")
cursorObj.close()
connection.close()

    Output

    Following is the output of the above code −

    updated successfully

    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

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

    MySQL – Delete Join

    Table of content

    Simple deletion operation in MySQL can be performed on a single entity or multiple entities of a table. But what if this deletion operation is to be performed on multiple entities of multiple tables? This is where Joins come into picture.

    MySQL DELETE… JOIN

    As we have discussed in this tutorial previously, Joins are used to retrieve records from two or more tables, by combining columns of these tables based on the common fields. This merged data can be deleted with all the changes reflected in original tables.

    Syntax

    Following is the basic syntax of DELETE… JOIN statement in MySQL −

    DELETE table(s)
FROM table1 JOIN table2
ON table1.common_field = table2.common_field;

    We can use any join clause (INNER JOIN, LEFT JOIN, RIGHT JOIN etc.) while performing deletion.

    Example

    In this example, we first create a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

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

    Now insert values into this table using the INSERT statement as follows −

    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

    Let us create another table ORDERS, containing the details of orders made and the date they are made on.

    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 this 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

    The delete operation is performed by applying the DELETE… JOIN query on these tables.

    DELETE a
FROM CUSTOMERS AS a INNER JOIN ORDERS AS b
ON a.ID = b.CUSTOMER_ID;

    Verification

    To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables.

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

    DELETE… JOIN with WHERE Clause

    The ON clause in DELETE… JOIN query is used to apply constraints on the records. In addition to it, we can also use WHERE clause to make the filtration stricter. Observe the query below; here, we are trying to delete the records of customers, in the CUSTOMERS table, whose salary is lower than Rs. 2000.00.

    DELETE a
FROM CUSTOMERS AS a INNER JOIN ORDERS AS b
ON a.ID = b.CUSTOMER_ID
WHERE a.SALARY < 2000.00;

    Verification

    To verify whether the changes are reflected in the original tables or not, we will use the SELECT statement.

    The CUSTOMERS table after deletion is 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
    5 Hardik 27 Bhopal 8500.00
    6 Komal 22 Hyderabad 4500.00
    7 Muffy 24 Indore 10000.00

    Delete Join Using Client Program

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

    Syntax

    To perform Delete Join through a PHP program, we need to execute the DELETE statement with JOIN clause using the mysqli function query() as follows −

    $sql = ''DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author
$mysqli->query($sql);

    To perform Delete Join through a JavaScript program, we need to execute the DELETE statement with JOIN clause using the query() function of mysql2 library as follows −

    sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author";
con.query(sql);

    To perform Delete Join through a Java program, we need to execute the DELETE statement with JOIN clause using the JDBC function executeUpdate() as follows −

    String sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author";
statement.executeUpdate(sql);

    To perform Delete Join through a python program, we need to execute the DELETE statement with JOIN clause using the execute() function of the MySQL Connector/Python as follows −

    delete_join_query = "DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUST_ID"
cursorObj.execute(delete_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 = ''DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author
if ($mysqli->query($sql)) {
    printf("Join deleted successfully!.
    ");
}
if ($mysqli->errno) {
    printf("Join could not be deleted !.
    ", $mysqli->error);
}
$mysqli->close();

    Output

    The output obtained is as follows −

    Join deleted successfully!.

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

  //Delete Join
  sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

    Output

    The output produced is as follows −

    ResultSetHeader {
  fieldCount: 0,
  affectedRows: 2,
  insertId: 0,
  info: '''',
  serverStatus: 34,
  warningStatus: 0,
  changedRows: 0
}

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

public class DeleteJoin {
   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 Delete JOIN...!;
         String sql = "DELETE tutorials_tbl, tcount_tbl FROM tcount_tbl INNER JOIN tutorials_tbl ON tcount_tbl.tutorial_author = tutorials_tbl.tutorial_author";
         statement.executeUpdate(sql);
         System.out.println("JOIN Deleted successfully...!");
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

    Output

    The output obtained is as shown below −

    Connected successfully...!
JOIN Deleted successfully...!

    import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
delete_join_query = f"""DELETE a FROM CUSTOMERS AS a INNER JOIN ORDERS AS b ON a.ID = b.CUST_ID"""
cursorObj.execute(delete_join_query)
connection.commit()
print("deleted succesfully")
cursorObj.close()
connection.close()

    Output

    Following is the output of the above code −

    deleted successfully

    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

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

    MySQL – Unique Key

    Table of content

    A Unique Key in MySQL, when applied on a certain field of a database table, does not allow duplicate values to be inserted in that column, i.e. it is used to uniquely identify a record in a table.

    Usually, any relational database contains a lot of information stored in multiple tables and each table holds a huge number of records. When we are handling such huge amounts of data there is a chance of redundancy (duplicate records). SQL keys are a way to handle this issue.

    This Unique Key works as an alternative to the Primary Key constraint; as both unique and primary keys assure uniqueness in a column of a database table.

    Creating MySQL Unique Key

    We can create a Unique Key on a MySQL table column using the UNIQUE keyword, and it holds the following features −

    • Even though unique key is similar to the primary key in a table, it can accept a single NULL value unlike the primary key.
    • It cannot have duplicate values.
    • It can also be used as a foreign key in another table.
    • A table can have more than one Unique column.

    Syntax

    Following is the syntax to create a UNIQUE key constraint on a column in a table −

    CREATE TABLE table_name(
   column_name1 datatype UNIQUE,
   column_name2 datatype,
   ...
);

    As you observe, we just need to specify the keyword UNIQUE after the name of the desired column while creating a table using CREATE TABLE statement.

    Example

    In this example, let us create a table named CUSTOMERS and define a UNIQUE Key on one of its fields, ADDRESS. Look at the following query −

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

    Output

    The table structure displayed will contain a UNI index on the ADDRESS column as shown −

    Field Type Null Key Default Extra
    ID int NO NULL
    NAME varchar(20) NO NULL
    AGE int NO NULL
    ADDRESS char(25) YES UNI NULL
    SALARY decimal(18, 2) YES NULL

    As you can see in the table definition, the Unique Key is created on the ADDRESS field.

    Creating Multiple Unique Keys

    We can create one or more Unique Key constraints on a column in a single MySQL table. When this constraint is applied in multiple fields, one cannot insert duplicate values in those fields.

    Syntax

    Following is the syntax to create unique key constraints on multiple columns in a table −

    CREATE TABLE table_name(column_name1 UNIQUE, column_name2 UNIQUE,...)

    Example

    Assume we have created another table with the name CUSTOMERS in the MySQL database using CREATE TABLE statement.

    Here we are creating a UNIQUE constraint on columns NAME and ADDRESS using the UNIQUE keyword as shown below −

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

    Output

    The table structure displayed will contain a UNI index on the ADDRESS column as shown −

    Field Type Null Key Default Extra
    ID int NO NULL
    NAME varchar(20) NO UNI NULL
    AGE int NO NULL
    ADDRESS char(25) YES UNI NULL
    SALARY decimal(18, 2) YES NULL

    Creating Unique Key on Existing Columns

    We can add a unique key constraint on an existing column of a table using the ALTER TABLE… ADD CONSTRAINT statement.

    Syntax

    Following is the syntax to create a UNIQUE Key on existing columns of a table −

    ALTER TABLE table_name
ADD CONSTRAINT unique_key_name
UNIQUE (column_name);

    Note − Here the UNIQUE_KEY_NAME is just the name of the Unique Key. It is optional to specify the name while creating a unique key. It is used to drop the constraint from the column in a table.

    Example

    Using the ALTER TABLE statement, you can add a UNIQUE constraint on any existing column in the CUSTOMERS table created previously. In the following example, we are applying the UNIQUE constraint on the NAME column as shown below −

    ALTER TABLE CUSTOMERS
ADD CONSTRAINT UNIQUE_NAME
UNIQUE (NAME);

    Output

    The table structure displayed will contain a UNI index on the ADDRESS column as shown −

    Field Type Null Key Default Extra
    ID int NO NULL
    NAME varchar(20) NO UNI NULL
    AGE int NO NULL
    ADDRESS char(25) YES NULL
    SALARY decimal(18, 2) YES NULL

    Dropping MySQL Unique Key

    If there is an unique constraint on a column already, you can drop it whenever it is not needed. To drop the Unique Constraint from the column of a table you need to use the ALTER TABLE statement again.

    Syntax

    Following is the SQL query to drop the UNIQUE constraint from the column of a table −

    ALTER TABLE TABLE_NAME DROP CONSTRAINT UNIQUE_KEY_NAME;

    Example

    In this example, we will drop the constraint named UNIQUE_NAME from the column NAME of the CUSTOMERS table using the following MySQL query −

    ALTER TABLE CUSTOMERS DROP CONSTRAINT UNIQUE_NAME;

    Output

    The table structure displayed will contain a UNI index only on the ADDRESS column, referring that the index on NAME column is removed.

    Field Type Null Key Default Extra
    ID int NO NULL
    NAME varchar(20) NO NULL
    AGE int NO NULL
    ADDRESS char(25) YES NULL
    SALARY decimal(18, 2) YES NULL

    Creating Unique Key Using Client Program

    In addition to use a key on a column to identify uniquely using the MySQL query We can also apply a Unique Key constraint on a Field using a client program.

    Syntax

    To apply unique key on a table field through a PHP program, we need to execute the CREATE TABLE statement with the UNIQUE keyword using the mysqli function query() as follows −

    $sql = ''CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30))
$mysqli->query($sql);

    To apply unique key on a table field through a JavaScript program, we need to execute the CREATE TABLE statement with the UNIQUE keyword using the query() function of mysql2 library as follows −

    sql = "CREATE TABLE customers(cust_ID INT NOT NULL, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT, PRIMARY KEY(cust_login_ID))";
con.query(sql);

    To apply unique key on a table field through a Java program, we need to execute the CREATE TABLE statement with the UNIQUE keyword using the JDBC function execute() as follows −

    String sql = "CREATE TABLE customers(Cust_ID INT NOT NULL UNIQUE, Cust_Name VARCHAR(30))";
statement.execute(sql);

    To apply unique key on a table field through a python program, we need to execute the CREATE TABLE statement with the UNIQUE keyword using the execute() function of the MySQL Connector/Python as follows −

    unique_key_query = ''CREATE TABLE TEST1 (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, MOBILE BIGINT UNIQUE, AADHAR BIGINT UNIQUE, AGE INT NOT NULL)''
cursorObj.execute(unique_key_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 = ''CREATE TABLE customers(cust_ID INT NOT NULL UNIQUE, cust_Name VARCHAR(30))
if ($mysqli->query($sql)) {
    echo "Unique column created successfully in customers table n";
}
if ($mysqli->errno) {
    printf("Table could not be created!.
    ", $mysqli->error);
}
$mysqli->close();

    Output

    The output obtained is as follows −

    Unique column created successfully in customers table

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

  //create a table that stores primary key!
  sql = "CREATE TABLE customers(cust_ID INT NOT NULL, cust_Name VARCHAR(30), cust_login_ID INT AUTO_INCREMENT, PRIMARY KEY(cust_login_ID))";
  con.query(sql);

  //describe table details
  sql = "DESCRIBE TABLE customers";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});

    Output

    The output produced is as follows −

    [
    {
      id: 1,
      select_type: ''SIMPLE'',
      table: ''customers'',
      partitions: null,
      type: ''ALL'',
      possible_keys: null,
      key: null,
      key_len: null,
      ref: null,
      rows: 1,
      filtered: 100,
      Extra: null
    }
]

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

public class UniqueKey {
   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...!");

         //Create a unique key in the customer table...!;
         String sql = "CREATE TABLE customers(Cust_ID INT NOT NULL UNIQUE, Cust_Name VARCHAR(30))";
         statement.execute(sql);
         System.out.println("Unique key created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE customers");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2)+" "
                    +resultSet.getString(3)+ " "+ resultSet.getString(4));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

    Output

    The output obtained is as shown below −

    Connected successfully...!
Unique key created successfully...!
Cust_ID int NO PRI null
Cust_Name varchar(30) YES  null

    import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
# Create table
unique_key_query = ''CREATE TABLE TEST1 (ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, MOBILE BIGINT UNIQUE, AADHAR BIGINT UNIQUE, AGE INT NOT NULL)''
cursorObj.execute(unique_key_query)
connection.commit()
print("Unique key column is created successfully!")
cursorObj.close()
connection.close()

    Output

    Following is the output of the above code −

    Unique key column is created successfully!

    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

  • 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