CODEWITHSUNDEEP
mysqlsql
jared_flack
jared_flack

Reputation: 1646

Most efficient SQL, DISTINCT or WHERE...AND

Both of these work, but is there a better way to write this?

1.

SELECT asset_id,
       asset.category_id,
       x,
       y
FROM asset
INNER JOIN map_category
ON map_category.category_id = asset.category_id
WHERE asset.map_id = 5
AND map_category.map_id = 5

2. (Added DISTINCT and removed last line)

SELECT DISTINCT asset_id,
       asset.category_id,
       x,
       y
FROM asset
INNER JOIN map_category
ON map_category.category_id = asset.category_id
WHERE asset.map_id = 5

Without either DISTINCT or the last line AND map_cate..., I get 3 records. One for each:

map_category table

map_category

asset table

asset

Upvotes: 3

Views: 268

Answers (2)

Seagull
Seagull

Reputation: 3600

These two queries do completely different things. DISTINCT selects only unique asset_id rows and another query selects only rows where asset.map_id = 5.

The reason you have the same result is your data. On some other data you will have completely different results. So you can't compare efficiency.

Upvotes: 4

Aprillion
Aprillion

Reputation: 22322

since your foreign key consists of both the columns, you should join on both columns...

SELECT asset_id,
   asset.category_id,
   x,
   y
FROM asset
INNER JOIN map_category
ON map_category.category_id = asset.category_id
AND asset.map_id = map_category.map_id

Upvotes: 2

Related Questions