mysql

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

Posted on by user

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

user