I am trying to optimize a function (MySQL), but the JOIN is still not completely understood (I try to make a simple example) :

I have 2 tables :

Table ITEMS

The description of objects are linked to the ITEM table by ID_ORDER, and NAME.(Not possible to have 2 items with same name inside the same contract).

Table DESCRIPTION :

Now, I need to know all items I have in the contract, with their description, and I use this query :

SELECT * FROM items INNER JOIN description 
ON (description.ID_ORDER=items.ID_ORDER AND description.NAME=items.NAME) 
WHERE ID_ORDER= 2564

The question is,how does SQL work with such sentence?

For example in previous example of SPOON_2, the sentence may return 2 records :

What behaviour does SQL have? I think the first one (as I understood the INNER JOIN), and I would like to have second behaviour (maybe RIGHT JOIN, but not sure).

1. It founded 2 items "SPOON_2" in contract, then for each of them, it will look for its description

2. It founded 2 items, but as it recognisez them as equal description (same ID_ORDER AND NAME), it will read description only once, then apply it to the 2 items.

CodePudding user response：

You can use the EXPLAIN command, that will let you know how MySQL is planning to answer your query. You can use it to optimise your query to run faster, or just to understand how it works. See https://dev.mysql.com/doc/refman/8.0/en/explain.html

For example:

EXPLAIN SELECT * FROM items INNER JOIN description 
ON (description.ID_ORDER=items.ID_ORDER AND description.NAME=items.NAME) 
WHERE ID_ORDER=2564

CodePudding user response：

As in you sample your rows are not duplicated beacuse the value fo the column ID is different 002 , 004

If you need a result with a reduced set of data then you must select only the column you really need and apply the distinct clause

select DISTINCT ID_ORDER, ID_BOX , NAME, LENGTH, WIDTH 
FROM items 
INNER JOIN description ON description.ID_CONTRACT=items.ID_CONTRACT 
    AND description.NAME=items.NAME
WHERE ID_CONTRACT= 2564