Fetch similar ID records from two tables in MySQL

MySQLMySQLi Database

Let us first create a table −

mysql> create table DemoTable1
(
   Id int
);
Query OK, 0 rows affected (1.26 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1 values(100);
Query OK, 1 row affected (0.20 sec)
mysql> insert into DemoTable1 values(110);
Query OK, 1 row affected (0.49 sec)
mysql> insert into DemoTable1 values(4);
Query OK, 1 row affected (0.44 sec)
mysql> insert into DemoTable1 values(3);
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable1;

This will produce the following output −

+------+
| Id   |
+------+
| 100  |
| 110  |
| 4    |
| 3    |
+------+
4 rows in set (0.00 sec)

Following is the query to create the second table −

mysql> create table DemoTable2
(
   Id int,
   Name varchar(50)
);
Query OK, 0 rows affected (0.78 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable2 values(4,'Chris');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable2 values(1,'David');
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable2 values(110,'Adam');
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable2 values(210,'Bob');
Query OK, 1 row affected (0.19 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable2;

This will produce the following output −

+------+-------+
| Id   | Name  |
+------+-------+
| 4    | Chris |
| 1    | David |
| 110  | Adam  |
| 210  | Bob   |
+------+-------+
4 rows in set (0.00 sec)

Following is the query to fetch similar ID records from two tables in MySQL −

mysql> select Id,Name from DemoTable2
where Id IN (select Id from DemoTable1);

This will produce the following output −

+------+-------+
| Id   | Name  |
+------+-------+
| 110  | Adam  |
| 4    | Chris |
+------+-------+
2 rows in set (0.39 sec)
raja
Published on 03-Oct-2019 10:13:40
Advertisements