MySQL - single query vs multiple queries between different-unrelated tables

Question

I need to get two column from two different and unrelated tables, there are two ways to do that:

Send 2 separate query:

SELECT * FROM table_a WHERE id = "";

SELECT * FROM table_b WHERE id = "";
Send 1 single query:

SELECT a.*, b.* FROM table_a AS a, table_b AS b WHERE a.id = "" AND b.id = "";
- I wonder if there are the same or there is one that better than the other?
- If there is no clear better one, which on do you prefer and why?

Updated:

I deliberately query by ID therefore only 1 result from each table

Second way will give you a Cartesian join of the two tables. So they are not equivalent. — Gurwinder Singh
– Gurwinder Singh, Commented Apr 11, 2018 at 3:40
You should show us sample data and expected result. The 2nd question is depend on what result you want. For e.g, if 1st query you got 3 rows, 2nd query 5 rows then what expected output do you want from that, in what order... — Pham X. Bach
– Pham X. Bach, Commented Apr 11, 2018 at 3:43
The first query will give you two result sets from each of the two tables. The second query joins the tables based on id. Show us your expected output. — Tim Biegeleisen
– Tim Biegeleisen, Commented Apr 11, 2018 at 3:53
@GurV I need data from both rows, so from my POV it is the same — kkkkkkk
– kkkkkkk, Commented Apr 11, 2018 at 4:07
Simply just use a union to get all on two row unless you need to get one row — Amin
– Amin, Commented Apr 11, 2018 at 4:15

Rakesh Soni · Accepted Answer · 2018-04-11 04:55:04Z

2

Both the ways you mentioned may return the differnt result set and it depends on the data exist in your table.

But if you are sure that each query returns only single row then

Two separate queries are better than join

SELECT * FROM table_a WHERE id = "";
SELECT * FROM table_b WHERE id = "";

You can use something like below as well

SELECT * FROM table_a WHERE id = ""
UNION 
SELECT * FROM table_b WHERE id = "";

Join is always costly from the performance point of view

Now consider your query (how it may return the different number of record)

SELECT a.*, b.*
FROM table_a AS a, table_b AS b
WHERE a.id = "" AND b.id = "";

Assume if there are 2 rows in table_a where id="" and 3 rows in table_b where id =""

Then DB will return the 6 rows in the result set in case of this join query (2 X 3 = 6). While in the way#1 you mentioned above DB will return the 5 rows only (2 + 3)

Also the number of columns will be different in result set in both the way If there are 2 columns in the table_a and 2 columns in the table_b then Way#1 will contain the 2 columns in the resultset only while in way#2 there will be 4 columns in the result set(2 from each table)

answered Apr 11, 2018 at 4:55

Rakesh Soni

11k6 gold badges48 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

kkkkkkk Over a year ago

Thanks for your answer. How about two queries vs UNION query? which is better or just the same? I think the UNION one is better, because it saves one more query to database

Collectives™ on Stack Overflow

MySQL - single query vs multiple queries between different-unrelated tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related