- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to use SELF JOIN in MySQL?
To use SELF JOIN, let us create a table. The query to create a table is as follows −
mysql> create table SelfJoinDemo -> ( -> Id int NOT NULL AUTO_INCREMENT PRIMARY KEY, -> CountryName varchar(20), -> CountryRank int, -> `Year` varchar(10) -> ); Query OK, 0 rows affected (1.02 sec)
Now you can insert some records in the table using insert command. The query is as follows −
mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('US',1,'2016'); Query OK, 1 row affected (0.12 sec) mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('UK',5,'2013'); Query OK, 1 row affected (0.16 sec) mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('France',45,'2010'); Query OK, 1 row affected (0.21 sec) mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('Turkey',3,'2000'); Query OK, 1 row affected (0.17 sec) mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('Japan',78,'1995'); Query OK, 1 row affected (0.21 sec) mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('Romania',110,'2007'); Query OK, 1 row affected (0.22 sec) mysql> insert into SelfJoinDemo(CountryName,CountryRank,`Year`) values('UK',3,'2000'); Query OK, 1 row affected (0.16 sec)
Display all records from the table using a select statement. The query is as follows −
mysql> select *from SelfJoinDemo;
The following is the output −
+----+-------------+-------------+------+ | Id | CountryName | CountryRank | Year | +----+-------------+-------------+------+ | 1 | US | 1 | 2016 | | 2 | UK | 5 | 2013 | | 3 | France | 45 | 2010 | | 4 | Turkey | 3 | 2000 | | 5 | Japan | 78 | 1995 | | 6 | Romania | 110 | 2007 | | 7 | UK | 3 | 2000 | +----+-------------+-------------+------+ 7 rows in set (0.00 sec)
Here is the query of SELF JOIN −
mysql> SELECT DISTINCT t1.CountryName, t2.Year -> FROM SelfJoinDemo AS t1, -> SelfJoinDemo AS t2 -> WHERE t1.Year=t2.Year -> and t1.CountryName='US';
The following is the output −
+-------------+------+ | CountryName | Year | +-------------+------+ | US | 2016 | +-------------+------+ 1 row in set (0.00 sec
- Related Articles
- How to use Straight Join in MySQL?
- How can you perform self join on two tables using MySQL in Python?
- How to use MySQL JOIN without ON condition?
- How to apply CROSS JOIN correctly in MySQL?
- How can we distinguish between MySQL CROSS JOIN and INNER JOIN?
- How to make use of Join with LINQ and Lambda in C#?
- How to use spread operator to join two or more arrays in JavaScript?
- How can we use MySQL self-computed output from any expression, function etc. for inserting values in a row?
- Requirements to use a text join in SAP HANA
- How to build self-confidence?
- How MySQL LEFT JOIN can be used to simulate the MySQL MINUS query?
- MySQL join two tables?
- How to use Coalesce in MySQL?
- How to join tables and fetch values from a MySQL database?
- How to improve your self-esteem?

Advertisements