0

I have two tables, ONE and MANY.

ONE.listId (int, not null, primary or unique) is related to MANY.listId (int, not null) via foreign key constraint with CASCADE on delete/update.

When SELECTing over this relationship via:

SELECT
    ONE.field AS parent,
    MANY.field AS child
FROM
    ONE
    [JOIN] MANY ON ONE.listId = MANY.listId;

Avoiding production of rows with NULLs in the output, what is the "recommended" join to use in MySQL for optimal performance? INNER? RIGHT?

Given the relationship between the fields, both JOINs will produce the same result. If the optimiser sees this and produces the same strategy for both joins, what is the "traditional" join type to specify in such a situation?

Mark K Cowan
  • 1,755
  • 1
  • 20
  • 28
  • 1
    An outer join doesn't produce the same result as an inner join, if there's no corresponding row in your `MANY` table to a given listId in `ONE`. And a foreign key constraint doesn't imply that there must be corresponding rows in your `MANY` table. – VMai May 05 '14 at 09:39
  • Good point, excluding OUTER JOINs then, what is recommended? – Mark K Cowan May 05 '14 at 10:07
  • @MarkKCowan . . . What do you mean? Most people would recommend proper ANSI `join` syntax, using either `JOIN` or `INNER JOIN` as the key word. Beyond that, what are you looking for? – Gordon Linoff May 05 '14 at 10:28
  • @Gordon: given the constraints,I'm looking for the optimal join to use with respect to performance. – Mark K Cowan May 05 '14 at 11:59

0 Answers0