Course
Self Join
MySQL Tutorial
This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.
Self Join
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 bWHERE 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 −
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 bWHERE a.SALARY < b.SALARY;
Output
The resultant table displayed will list out all the customers that earn lesser than other customers −
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 bWHERE a.common_field = b.common_fieldORDER 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 bWHERE a.SALARY < b.SALARY ORDER BY a.SALARY;
Output
The resultant table is displayed as follows −
Self Join Using Client Program
We can also perform the Self join operation on one or more tables using a client program.
Syntax
PHPNodeJSJavaPython
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);
Example
Following are the programs −
PHPNodeJSJavaPython
$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