More on JOINS
06/01/2001In the last column, I introduced you to one of the most important workhorse commands in SQL -- the JOIN command. Now we'll take a good look at what joins are and how to create them by focusing on the INNER JOIN, the most commonly used SQL join.
Because the JOIN clause is probably one of the most used and most confusing parts of SQL, I'll do my best to demystify it over the next few columns.
So, tell me again, what's a Join?
One of the fundamental precepts of SQL is that databases are relational. As we discussed in previous columns on database design, one of the goals of good database development is to use relationships to decompose data tables into the most atomic constituents possible.
While that's the point of good database design, the point of a good SQL statement is to reverse the process and pull that data back into some semblance of its original shape. In essence, we must create a virtual table from the information stored in two (or more!) difference tables in the database.
As I discussed last week, you can use the SELECT ... WHERE statement to combine two different tables on a common field:
SELECT * FROM Artist,CD WHERE (CD.ArtistID=Artist.ArtistID)
which can be translated into the equivalent JOIN statement
SELECT * FROM Artist INNER JOIN CD ON CD.ArtistID=Artist.ArtistID
|
Also in aboutSQL: |
In both cases, we're using a SELECT statement to pull data from the tables in the database, so the only real difference is syntax, right? Well, yes. These queries test the equality of the tables -- finding the places where the two columns in the join overlap. So obviously the question becomes ...
Why use the JOIN statement at all?
Well, there isn't a completely compelling reason to prefer one way of writing a JOIN command over another. Didn't think that would be my answer, did you? But's important that you understand that they are equivalent statements if you work with SQL created by other people -- as long as you can translate in your head you should be fine.
BUT ... Oracle doesn't give you the option of using the INNER JOIN syntax. Oracle uses the symbolic syntax exclusively and will throw an error if you try to use the inner join method. Most other databases are more forgiving.
One argument in favor of using the explicit JOIN syntax, especially as we learn more about the various kinds of joins, is that the text makes it explicit which kind of join we're trying. I find x LEFT OUTER JOIN y clearer than the symbolic x *= y. The downside of this approach is that your SQL code will simply not work in Oracle. Here's one area where your choice of databases makes a difference in how you'd develop SQL code.
A little more on syntax
You'll notice that in both examples of the SQL JOIN, the join was performed where
CD.ArtistID=Artist.ArtistID
Note that we've fully-qualified the fields that will be joined -- this is required whenever a column name is ambiguous. This has not been a problem when we've been dealing with single tables (DBMSes make sure column names are unique in a single table), but will become an increasingly common issue as we join tables together. DBMSes will generate an error when the query is executed if the column names are ambiguous.
While we're looking at syntax, we can also examine using an alias for a table name. Typing SQL statements like:
SELECT * FROM Artist,CD WHERE CD.ArtistID=Artist.ArtistID
can become more time-consuming as more tables are added to the JOIN statement. It's extremely common to see DBAs to create queries that have shorter table names by aliasing the table name to a one- or two-letter abbreviation. For example,
SELECT * FROM Artist AS A, CD AS C WHERE C.ArtistID=A.ArtistID
The AS statement can be used in many DBMS to assign an alias to a table name that can be used throughout the SQL statement. Of course, Oracle is slightly different (and many other databases support this notation as well).
SELECT * FROM Artist A, CD C WHERE C.ArtistID=A.ArtistID
No AS keyword, otherwise everything is the same.
If we were after just the name of the artist and the album for a summary list, we could combine all these techniques together into the following block of SQL
SELECT A.ArtistName, C.Title FROM Artist A, CD C WHERE C.ArtistID=A.ArtistID
This doesn't look like that much of a savings, but wait until we're writing complex multi-table joins with aggregate functions and filtering rules and then you'll appreciate these tips!
Next steps
In this article, I showed you a little bit about the INNER JOIN statement and some general syntax that applies to all of the SQL JOIN statements. Next week, I'll jump into the various other types of joins -- CROSS, LEFT OUTER, RIGHT OUTER, and NATURAL.
Until then, feel free to contact me with comments and questions about this column, or use the Talk Back feature to share your comments with other readers. Believe it or not, I read all the e-mail you send me and answer the vast majority of it. I'm contemplating using this column to address real-world SQL problems -- be it a thorny SQL challenge, a poorly written SQL query you inherited from another developer, or a newbie question.
If you'd like to see that sort of analysis, let me know -- and if you need that sort of analysis, drop me a line. I won't promise to solve your problem, but I'd like to see what questions are out there. I've gotten some mail already and am incorporating the suggestions into planning for future columns.
John Paul Ashenfelter is president and CTO of TransitionPoint.com, a technology development and analysis group focusing on web database applications.
Read more aboutSQL columns.
Return to ONLamp.com.
Showing messages 1 through 16 of 16.
-
Join for more than one values in foreign key field.
2005-06-08 08:28:47 MIGSSQL [View]
-
Selecting Recordset
2003-03-29 02:29:11 nithyaseshadri [View]
Dear Mr.John Paul
With reference to your article - More on Joins dated 06/01/2001 ..there was a mention about SAVING by specifying the column names to be selected instead of using Select * from table..
I totally agree with it and would like to know in detail as to how this will effect when dealing with multitable complex-queries...
Please explain me the behavior of recordset with respect to MEMORY & SPEED in detail.
Thanking you
Nithya Seshadri
-
Right outer joins in MS SQL SERVER
2002-05-01 02:13:54 sanjayk [View]
I am interested in knowing how the right outer joins work in sql server. I noticed the difference in behavior between the =* and the phrase "right outer join". The result set is different. Please go through the T-sql statements shown below.
select a.au_id, b.title, c.qty
from titleauthor a, titles b, sales c
where (a.title_id =* b.title_id)
and (a.title_id =* c.title_id)
select a.au_id, b.title, c.qty
from titleauthor a
right outer join titles b
on (a.title_id = b.title_id )
right outer join sales c
on (a.title_id = c.title_id )
The first results into 391 rows in pubs database of sql-server 2000 and the second produces 34 rows. Can you through some light on this?
-
Right outer joins in MS SQL SERVER
2003-07-10 11:59:34 anonymous2 [View]
The first query works as a cross join because you are not telling the system what type of join you want to use. The second one is written properly. -
Right outer joins in MS SQL SERVER
2006-01-28 23:31:14 JCrespin [View]
The answer given is correct. There is a cross join between table titles and table sales as you have not defined the join criteria.
If you want to receive the same result that you receive with the right outer join query you have to define the join between titles and sales (see below)...
select a.au_id, b.title,
c.qty
from titleauthor a,
titles b,
sales c
where
(a.title_id =* b.title_id)
and (a.title_id =* c.title_id)
and (b.title_id = c.title_id)
order by a.title_id
That said, definitely use the second query.
-
practical Examples Of Outer Joins And Self Joins
2002-03-18 17:09:38 latinbeast19 [View]
I owuld like to see the code that you would use to do Outer Joins and self joins.thanks -
practical Examples Of Outer Joins And Self Joins
2008-07-25 21:55:55 to know about database [View]
dear sir
give me question from join statement. -
practical Examples Of Outer Joins And Self Joins
2006-07-13 02:18:31 olivekumardey [View]
plz show the entire coding of both self and outer joins -
practical Examples Of Outer Joins And Self Joins
2004-04-15 01:41:50 juby [View]
some clear examples of outer joins
-
left join
2002-01-26 01:41:59 zakirtcs [View]
Hi,
i would like to know about left joins and Where it can be used in MS SQL7.0.
It is nice of you if i can get some more information on joins.
-
Outer Joins With Oracle
2001-09-07 12:43:30 boyd4715 [View]
I Believe that the *= is non ansi standard on doing joins. Oracle uses (+). This is place next to the column. So
a.foo = b.foo(+) would do a left outer join
a.foo(+) = b.foo woudl do a right outer join -
Outer Joins With Oracle
2004-04-15 01:43:31 juby [View]
outer joins
-
Example for Cross Join
2001-06-08 06:09:25 wallinbl [View]
Can you provide an example of when you would use a cross join when you write that article? I've often wondered what that would be useful for. -
Example for Cross Join
2007-01-29 21:36:38 sv1241 [View]
create table table_one (col_one number, col_two varchar2(10));
create table table_two (col_three number, col_four varchar2(10));
insert into table_one values ( 1, 'one');
insert into table_one values ( 2, 'two');
insert into table_two values (10, 'ten');
insert into table_two values (20, 'twenty');
insert into table_two values ( 5, 'five');
select * from
table_one cross join
table_two;
Each row from table_one is returned together with each row from table_two:
-
Relationship: one to one
2001-06-04 16:22:32 edith30 [View]
I'm new on sql, please explain more about how can I make the selects statement in order to output the fields from diferents tables at the same time in this kind of relationship (one to one).
Thanks. -
Relationship: one to one
2001-06-08 06:14:43 wallinbl [View]
If you have a Customer Table (with columns CustKey, CustFirstName, CustLastName) and an Address Table (with columns AddrKey, CustKey, AddrLine1, AddrLine2), to get all fields from both tables, you would write a query like this:
SELECT * FROM Customer INNER JOIN Address ON
Customer.CustKey = Address.CustKey
This statement would return the fields: Customer.Custkey, Customer.CustFirstName, Customer.CustLastName, Address.AddrKey, Address.CustKey, Address.AddrLine1, Address.AddrLine2.
e.g. The foreign key table has three records for Country=India and two records for State=Punjab but the third record has State=*, one record has City=Chandigarh, second record has City=Ludhiana and third record has City=*. Now when I try searching for record having Country=India and State=Delhi and City=Delhi, I expect the record from foreign key table having Country=India and State=* and City=* but when I try searching the record with Country=India and State=Punjab and City=Chandigarh then I expect the exact record exists in foreign key table.
Please guide me to come out of this trap
Thanks
SQLGUY