• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Is left outer join performed on more than two tables?

 
Rajeev Srikhar
Ranch Hand
Posts: 75
Chrome Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I know that joins(left, right, full) is performed between two tables. But I want to know whether Left outer join is performed on more than two tables? I'm asking this out of curiosity.

--Thanks,
-Rajeev
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Likes 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
A "join" means there is more than one object listed in the FROM clause. This can be the same table listed more than once, sub-queries, views, or any amount of tables, though regardless of what the object is, it is usually referred to as a table.

Each table is joined to every other table in the FROM clause, much like in a multiplication equation, where each multiplicand is multiplied by every other one in the equation. An "inner" or "outer" join refers to what is done between two specific tables when (due to a WHERE clause) no records are found in the "other" table. Some databases group sets of tables together with parenthesis to add clarity. But that is misleading; logically, each table is separately joined with each other table.

A query can have multiple joins, and each join can be "inner" or "outer". So, to answer your question of can an outer join be done between more than two tables, the answer is both technically and logically no. That being said, being a statement can include more than one join, it is likely the answer to the question you meant to ask is yes.
 
Tim Holloway
Saloon Keeper
Posts: 18359
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think that Brian may have a cross-product (Cartesian) join confused with an Outer Join.

A cross-product join results in as many rows as the products of the row counts for each table row selected from. This is unfortunately the naive syntax for SQL and almost never what you actually want.

An Outer join has an anchor table which is joined on a matching query with null values returned when the anchor table can't pair up a row in the query with a corresponding row from the secondary table. As opposed to an Inner join, where only the anchor table rows that had corresponding matches with secondary table rows are returned.

And yes, you can very definitely join several tables together in a single SQL query. I have to do so far more often than I could wish, and it's one of the advantages of using an ORM system instead of raw JDBC - you don't have to kill yourself defining the join logic properly.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim Holloway wrote:I think that Brian may have a cross-product (Cartesian) join confused with an Outer Join.

I am not confusing anything. The only difference between a Cartesian join and an Outer join is which rows are excluded by the WHERE (or ON sub-) clause. The join in-and-of-itself is exactly the same.

The ANSI syntax is what confuses what is actually being done, and some people think a VENN diagram best explains it. I pity those people who have no idea what is actually happening, as they get confused with every bit of added complexity in the statement or logic.

Classic join syntax is done in the WHERE clause, where it belongs, as they are much more accurately reflect what is being done, both logically and by the database engine.
 
Tim Holloway
Saloon Keeper
Posts: 18359
56
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Trust me. There's a big difference between cross-product and outer joins. I had the questionable joy of seeing a cross-product join running on gigabytes of data. Over a LAN. In FoxPro. Estimated time to complete was in days.
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim, without going into join mechanics, i think we'll just have to agree to disagree.
 
Ryan McGuire
Ranch Hand
Posts: 1093
5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
To get back to the original question...

Is this what you're thinking of?



Any student without any addresses would show up as 1234 null null.
A student with an address but no phone number at that address will show up as 2345 4567 null
A student with an address AND a phone number at that address will have all three values filled in.

Disclaimer.  I'm using "raw" T*SQL / SQL Server, as opposed to JDBC. 
 
Dave Tolls
Ranch Hand
Posts: 2091
15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ryan McGuire wrote:
Disclaimer.  I'm using "raw" T*SQL / SQL Server, as opposed to JDBC. 


There isn't a difference is there?
JDBC is just a wrapper for passing SQL down to the server, each wrapper is specific to the database in question, so I'd be surprised if the SQL is different in any way.
(And that's just plain SQL isn't it?)
 
Brian Tkatch
Bartender
Posts: 567
25
Linux Notepad Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls wrote:
Ryan McGuire wrote:
Disclaimer.  I'm using "raw" T*SQL / SQL Server, as opposed to JDBC. 


There isn't a difference is there?
JDBC is just a wrapper for passing SQL down to the server, each wrapper is specific to the database in question, so I'd be surprised if the SQL is different in any way.
(And that's just plain SQL isn't it?)

Yep. I think he just wasn't sure.

FWIW, that is ANSI SQL, which most (but not all) RDBMS's implement. IIRC, even then, there's a RDBMS or two that require "LEFT OUTER" and not just "LEFT".
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic