Mysql – How to join two table and show one query result in MySQL

best practicesinnodbMySQLquery

I have a table1 (records 3), and table2 (records 3). Where i have field name in both. Now i want to make a result from those two table, which will show me both table records and take only one if there is duplicate. So my expected output records will contain 5 rows not 6 rows. How do i do that?

Example:

table1:                       table2:

+-------------------------+   +--------------------------------+
| Name                        | Name
---------------------------   +---------------------------------
| A    |                      | C    |
| B    |                      | D    |
| C    |                      | E    |


My Expected output is:

+-------------------------+
| Name | ID                
---------------------------
| A    | 1 table1
| B    | 2 table1
| C    | 3 table2 or table1 (no unique)
| D    | 4 table2
| E    | 5 table2

I tried this:

 SELECT 
  name as name 
 FROM 
  table1
UNION
 SELECT 
  anothernamename as name 
 FROM 
  table2
WHERE
   name like '%C%'

Error: #1054 - Unknown column 'name' in 'where clause'

Follow up: Union with Where clause + extra extra large requirement.

SELECT * FROM 
(
    ( 
     SELECT * FROM table1 
     WHERE ...
     ORDER BY ...
     LIMIT ...
    ) 
UNION 
    ( 
     SELECT * FROM table2 
     WHERE ...
     ORDER BY ...
     LIMIT ...
    )
) as t
WHERE ...
    ORDER BY ...

Best Answer

Here is the query you need:

SELECT name FROM
(
    SELECT name FROM table1
    UNION
    SELECT name FROM table2
) A;

Here is some sample code based on your question:

use test
drop table if exists table1;
drop table if exists table2;
create table table1
(
    id int not null auto_increment,
    name varchar(10),
    primary key (id)
);
create table table2 like table1;
insert into table1 (name) values ('A'),('B'),('C');
insert into table2 (name) values ('C'),('D'),('E');
SELECT name FROM
(
    SELECT name FROM table1
    UNION
    SELECT name FROM table2
) A;

Here is the execution of that sample code:

mysql> drop table if exists table1;
Query OK, 0 rows affected (0.03 sec)

mysql> drop table if exists table2;
Query OK, 0 rows affected (0.03 sec)

mysql> create table table1 (
    ->     id int not null auto_increment,
    ->     name varchar(10),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> create table table2 like table1;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into table1 (name) values ('A'),('B'),('C');
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into table2 (name) values ('C'),('D'),('E');
Query OK, 3 rows affected (0.11 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT name FROM (SELECT name FROM table1
    -> UNION SELECT name FROM table2) A;
+------+
| name |
+------+
| A    |
| B    |
| C    |
| D    |
| E    |
+------+
5 rows in set (0.00 sec)

mysql>

Give it a Try !!!

Related Question