can someone give me some insight or point me to some insight about sql joins (inner, outer, whatever)?
I sort of understand using keys etc, does this have anything to do with them?
cheers for any help
hmm, Joins can get pretty complicated but are fairly easy once you understand the concepts. Basically if you have two tables which are somehow related (ie students table and classes table, so students take classes) and you wanted to find out students and their associated classes you could use a join. Though there will probably be other alternatives.
The type of join you use depends on the information you're looking for. I'm most familiar with left, right and full joins.
A left join will take every record from the left table and associate it with some record from the right table. If no record exists in the right table, it will be padded with nulls (left and right are relative to where they appear in the query). Right and Full work in a similar manner (full will pad both right and left with nulls). I suggest www.google.com and run a search for SQL Joins or something.
hey kix, that's a great explanation
please allow me to polish it a bit
A left outer join will return every row from the left table and associate it with row(s) from the right table. If no associated row exists in the right table for a given row in the left, the columns in the result row which would have come from the right table will be set to null.
A right outer join is the same as a left outer join with the table names reversed.
A full outer join is like a left and right outer join, and may contain rows where the columns from either the left or the right table can be null (but obviously not both at the same time)
if i may, i'd like to describe some other joins --
An inner join returns rows only for associated rows, i.e. from both left and right tables. This is the most common join type, as typified by the older "table list" syntax --A cross join is the same as an inner join with the join condition missing (as anyone can explain who has inadvertently left off the WHERE clause in the preceding example)Code:select a.foo, b.bar from tablea a, tableb b where a.id = b.id
An equi-join uses equality for the join condition
A theta join uses some other condition, typically inequality
A natural join involves joins based on like-named columns, and is rare in the wild
those are all the ones i can think of
please holler if those explanations are not clear...
forgot one --
a self join is a table joined to itself, and will require table aliases and qualified column names
you can combine terms, too
for example, a ranking query, which returns the rank of a row (1st place, 2nd place, etc.) is an inner theta self-join --Code:select A.theValue , count(*) as theRank from theTable as A , theTable as B where A.theValue >= B.theValue group by A.theValue