What happens to JOIN if no colums from said table are SELECT-ed?-CodePudding

I am wondering how Oracle, or how DB managers, work through longer scripts and bigger result. I will give an example and some questions below:

SELECT order.nr, order.date, order.datesent, items.id, items.name
FROM orders
  LEFT JOIN items ON orders.itemid=items.id
  LEFT JOIN customer ON orders.custid=customer.id
  LEFT JOIN shipper ON order.shipperid=shipper.id
  LEFT JOIN production ON production.itemid=items.id
  LEFT JOIN sales ON sales.orderid=orders.id
  LEFT JOIN transport ON orders.transid=transport.id

Does the script run through all tables even though I don't use them in the SELECT?
Generally does this slow down the script execution if it brings back a lot of data?

CodePudding user response：

Does the script run through all tables even though I don't use them in the SELECT?

The SQL engine will process all the column values because it needs to know if there are duplicate id values in any of the table and then, if so, there will be duplicate rows in the output. However, it does not necessarily perform a full table scan as it could always use the indexes (assuming that you are joined on an indexed column).

It is theoretically possible that the optimizer could decide to not bother with the unnecessary join if the id column of the joined table has a UNIQUE index on it so there will never be any duplicates and the data is not returned; but it is equally possible that that optimization is not used or that the index guaranteeing that condition is not present.

The best solution is to look at the EXPLAIN PLAN for the query and then you will see what joins are actually performed and whether table or index scans are used.