MySQL - Using Joins



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

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

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

Types of Joins

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

  • Inner Join − An Inner Join retrieves the intersection of two tables. It compares each row of the first table with each row of the second table. If the pairs of these rows satisfy the join-predicate, they are joined together. This is a default join.

  • Outer Join − An Outer Join retrieves all the records in two tables even if there is no counterpart row of one table in another table, like Inner Join. Outer join is further divided into three subtypes: Left Join, Right Join and Full Join. We will learn about these Joins later in this tutorial.

Example

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

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

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

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

The table is created as −

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

ORDERS Table −

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

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

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

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

The table is displayed as follows −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Joining Tables −

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

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

Output

The table is displayed as follows −

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

Joins Using a Client Program

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

Syntax

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

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

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

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

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

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

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

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

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$dbname = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error); exit(); } printf('Connected successfully.
'); $sql = 'SELECT a.tutorial_id, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a JOIN tcount_tbl b ON a.tutorial_author = b.tutorial_author'; $result = $mysqli->query($sql); if ($result->num_rows > 0) { while ($row = $result->fetch_assoc()) { printf( "Id: %s, Author: %s, Count: %d
", $row["tutorial_id"], $row["tutorial_author"], $row["tutorial_count"] ); } } else { printf('No record found.
'); } mysqli_free_result($result); $mysqli->close();

Output

The output obtained is as follows −

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

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

Output

The output produced is as follows −

[ { tutorial_id: 1, tutorial_author: 'John Poul', tutorial_count: 2 } ]
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

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

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

Output

The output obtained is as shown below −

Connected successfully...!
Table records after join...!
1 John Paul 1
3 Sanjay 1 
import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
using_join_query = f"""SELECT ID, NAME, AGE, AMOUNT FROM CUSTOMERS JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUST_ID"""
cursorObj.execute(using_join_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

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