Many to one joins

Lets say I have the following tables:

create table table_a
(
  id_a,
  name_a,
  primary_key (id_a)
);

create table table_b
(
  id_b,
  id_a is not null, -- (Edit)
  name_b,
  primary_key (id_b),
  foreign_key (id_a) references table_a (id_a)
);

We can create a join view on these tables in a number of ways:

create view join_1 as
(
  select 
    b.id_b, 
    b.id_a, 
    b.name_b, 
    a.name_a 
  from table_a a, table_b b
  where a.id_a = b.id_a
);

create view join_2 as
(
  select 
    b.id_b, 
    b.id_a, 
    b.name_b, 
    a.name_a 
  from table_b b left outer join table_a a
  on a.id_a = b.id_a
);

create view join_3 as
(
  select 
    b.id_b, 
    b.id_a, 
    b.name_b, 
    (select a.name_a from table_a a where b.id_b = a.id_a) as name_a 
  from table_b b;
);

Here we know:

(1) There must be at least one entry from table_a with id_a (due to the foreign key in table B) AND

(2) At most one entry from table_a with id_a (due to the primary key on table A)

then we know that there is exactly one entry in table_a that links in with the join.

Now consider the following SQL:

select id_b, name_b from join_X;

Note that this selects no columns from table_a, and because we know in this join, table_b joins to exactly one we really shouldn't have to look at table_a when performing the above select.

So what is the best way to write the above join view?

Should I just use the standard join_1, and hope the optimizer figures out that there is no need to access table_a based on the primary and foreign keys?

Or is it better to write it like join_2 or even join_3, which makes it more explicit that there is exactly one row from the join for each row from table_b?

Edit + additional question

Is there any time I s开发者_开发技巧hould prefer a sub-select (as in join_3) over a normal join (as in join_1)?

Why are you using views at all for this purpose? If you want to get data from the table, get it from the table.

Or, if you need a view to translate some columns in the tables (such as coalescing NULLs into zeros), create a view over just the B table. This will also apply if some DBA wanna-be has implemented a policy that all selects must be via views rather than tables :-)

In both those cases, you don't have to worry about multi-table access.

Intuitively, I'd think join_1 will perform slightly slower, because your assumption that the optimiser can transform the join away is wrong as you didn't declare the table_b.id_a column to be NOT NULL. In fact, this means that (1) is wrong. table_b.id_a can be NULL. Even if you know it can't be, the optimiser doesn't know that.

As far as join_2 and join_3 is concerned, depending on your database, optimisation might be possible. The best way to find out is to run (Oracle syntax)

EXPLAIN select id_b, name_b from join_X;

And study the execution plan. It will tell you whether table_a was joined or not. On the other hand, if your view should be reusable, then I'd go for the plain join and forget about pre-mature optimisations. You can achieve better results with proper statistics and indexes, as a join operation is not always so expensive. But that depends on your statistics, of course.

1 + 2 are effectively identical under SQL Server.

I have never used [3], but it looks quite odd. I would strongly suspect the optimizer will make it equivalent to the other 2.

It's a good exercise to run all 3 statements and compare the execution plans produced.

So given identical performance, the clearest to read gets my vote - [2] is the standard where it is supported otherwise [1].

In your case, if you don't want any columns from A, why include Table_A in the statement anyway?

If it's simply a filter - i.e. only include rows from Table B where a row exists in Table A even though I don't want any cols from Table A, then all 3 syntaxes are fine, although you may find that use of IF EXISTS is more performant in some dbs:

 SELECT * from Table_B b WHERE EXISTS (SELECT 1 FROM Table_A a WHERE b.id_b = a.id_a)

although in my experience this is usual equivalent in performance to any of the others.

You also ask, then would you choose a subquery over the other expressions. This boils down to whether it's a CORRELATED subquery or not.

Basically - a correlated subquery has to be run once for every row in the outer statement - this is true of the above - for every row in Table B you must run the subquery against Table A.

If the subquery can be run just once

 SELECT * from Table_B b WHERE b.id_a IN (SELECT a.id_a FROM Table_A a WHERE a.id_a > 10)

Then the subquery is generally more performant than a join - although I suspect that some optimizers will still be able to spot this and reduce both to the same execution plan.

Again, the best thing to do is to run both statements, and compare execution plans.

Finally and most simply - given the FK you could just write:

 SELECT * From Table_B b WHERE b.id_a IS NOT NULL

It will depend on the platform.

SQL Server both analyses the logical implications of constraints (foreign keys, primary keys, etc) and expands VIEWs inline. This means that the 'irrelevant' portion of the VIEW's code is obsoleted by the optimiser. SQL Server would give the exact same execution plan for all three cases. (Note; there is a limit to the complexity that the optimiser can handle, but it can certainly handle this.)

Not all platforms are created equal, however.
- Some may not analyse constraints in the same way, assuming you coded the join for a reason
- Some may pre-compile the VIEW's execution/explain plan

As such, to determine behaviour, you must be aware of the specific platform's capabilties. In the vast majority of situations the optimiser is a complex beast, and so the best test is simply to try it and see.

EDIT

In response to your extra question, are correlated sub-queries every prefered? There is no simple answer, as it depends on the data and the logic you are trying to implement.

There are certainly cases int he past where I have used them, both to simplify a query's structure (for maintainability), but also to enable specific logic.

If the field table_b.id_a referenced many entries in table_a you may only want the name from the latest one. And you could implement that using (SELECT TOP 1 name_a FROM table_a WHERE id_a = table_b.id_a ORDER BY id_a DESC).

In short, it depends.
- On the query's logic
- On the data's structure
- On the code's final layout

More often than not I find it's not needed, but measurably often I find that it is a positive choice.

Note:

Depending on the correlated sub-query, it doesn't actually always get executed 'once for every record'. SQL Server, for example, expands the required logic to be executed in-line with the rest of the query. It's important to note that SQL code is processed/compiled/whatever before being executed. SQL is simply a method for articulating set based logic, which is then transformed into traditional loops, etc, using the most optimal algorithms available to the optimiser.

Other RDBMS may perform differently, due to the capabilities or limitations of the optimiser. Some RDBMS perform well when using IN (SELECT blah FROM blah), or when using EXISTS (SELECT * FROM blah), butsome perform terribly. The same applies to correlated sub-queries. Sub perform exceptionally well with them, some don't perform so well, but most handle the very well in my experience.