Most efficient SQL, DISTINCT or WHERE...AND

Question

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

asset table

Answer 1

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.

Answer 2

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