CODEWITHSUNDEEP
mysql
Robert Sinclair
Robert Sinclair

Reputation: 5416

select statement from 2 unrelated tables

There's 2 unconnected tables, with no common IDs

+---------------------+
| names               |
+------+--------------+
| name        | lastN |
+-------------+-------+
| Anthony     | monta |
| Ryo         | shizu |
+------+--------------+

+----------------------+
| nicknames            |
+------+---------------+
| nickname             |
+------+---------------+
| miso_hungry          |
+------+---------------+

I'm trying to run a select query on both tables and currently doing something like:

SELECT names.name, nicknames.nickname
FROM names, nicknames
WHERE names.name="miso_hungry" OR nicknames.nickname="miso_hungry"

I'm getting back a weird results with repeating identical rows, which doesn't make sense.

For example if I search for miso_hungry with the query above it will return every row of "names" table for some reason and append the correct rows from the "nicknames" table..

Attaching a screenshot of the results

enter image description here

Above should show "NULL" under name column, since "miso_hungry" is not found in that column and I'm not sure why it prints every row of the "names" table also.

Upvotes: 0

Views: 494

Answers (2)

Ashraful Islam
Ashraful Islam

Reputation: 12830

You can use UNION Clause

  • Each SELECT statement within UNION must have the same number of columns
  • The columns must also have similar data types
  • The columns in each SELECT statement must also be in the same order

So we need to made them satisfy above condition. We can use Aliasing to do this. 

SELECT name,(SELECT NULL) as nickname FROM names WHERE name = "miso_hungry"

UNION

SELECT (SELECT NULL) as name, nickname FROM nicknames WHERE nickname = "miso_hungry"

Edited

If you want to get the match count from both table use query like below :

SELECT SUM(count) as count FROM ( 
    SELECT count(*) as count FROM names WHERE name = "miso_hungry"
    UNION ALL
    SELECT count(*) as count FROM nicknames WHERE nickname = "miso_hungry"
) both_table

Upvotes: 4

P.Salmon
P.Salmon

Reputation: 17640

The order of execution in your statement is from,where,select. With and implicit join you get a cartesian product which given

use sandbox;
create table n(name varchar(20));
create table nn(nickname varchar(20));

insert into n values('Antony'),('Ryo');
insert into nn values('miso');

results in

MariaDB [sandbox]> SELECT n.name, nn.nickname
    -> FROM n, nn;
+--------+----------+
| name   | nickname |
+--------+----------+
| Antony | miso     |
| Ryo    | miso     |
+--------+----------+
2 rows in set (0.00 sec)

The where clause is then applied - which yields the same result.

Upvotes: 1

Related Questions