Talk:Join (SQL)

From Wikipedia, the free encyclopedia

New comments at the bottom of the page, please.

Contents 1 SemiJoins 2 Implementation Section 3 Needs more detail 4 Images for join types 5 Cross Joins 6 Comma Separated Table lists is the FROM Clause is in the ANSI-92 Standard 7 Natural Joins and other points 8 Linking To This Page 9 Equi join example 10 Usefulness of examples 11 DDL

[edit] SemiJoins

what about semijoins?

From the article:

A semi join is an efficient join method where first the join attributed of one table are collected and reported to the second one. In was first reported in 1981. It can be improved with a Bloom-Filter (hashing).

Yeah, it could do with a lot more detail. Neilc 13:59, 16 Apr 2005 (UTC)

[edit] Implementation Section

Should the Implementation section (not the algorithms part) be moved to the Query optimizer page? I have already moved the join optimization section. Derekian 17:56, 8 Jun 2005 (UTC)

[edit] Needs more detail

This whole article needs more detail, especially on left-joins and semi-joins etc, which I came here to research. The Relational algebra page has some better sections on the theory, and SQL isn't really very far from that so having a seperate page on the SQL implementations seems a bit redundant. Maybe instead there should just be a page/section on how SQL differs from the RA, and how/if different products implement the different types. Maybe I'll have a go if I get good with it! Rodrigogo 17:24, 11 October 2005 (UTC)

Your suggestions sound nice. I suggest you go ahead and add it. TroelsArvin 08:22, 25 April 2006 (UTC)

[edit] Images for join types

It would be good if someone would add images to help people visualize how each type of join is evaluated. I'm thinking that there should be drawings with the overlapping rings, highlighting the solution set. I would be willing to create them, however, I do not have experience loading images into Wikipedia. DBBell 21:08, 14 December 2005 (UTC)

[edit] Cross Joins

Cross joins are also possible with most SQL implementations, and is identified on the Relational algebra page. DBBell 21:08, 14 December 2005 (UTC)

[edit] Comma Separated Table lists is the FROM Clause is in the ANSI-92 Standard

The article says joins of the follwing types

select * from t1,t2 where t1.c1=t2.c1

The ANSI-92 specification is on this site

http://www.contrib.andrew.cmu.edu/%7Eshadow/sql/sql1992.txt

And on pg 177, the comma separated table lists is mentioned

Also on the following site the BNF forms of the clauses are listed with comma separated table lists.

http://savage.net.au/SQL/

So in this article the so-called "non-standard" syntax is actually still a standard. Its not the cleanest way but still is standard.

AN 63.166.226.84 10 May 2006

[edit] Natural Joins and other points

The article is detailed and clear, but it neglects to discuss natural joins. Do other people agree that natural joins should be addressed in the article? If so, I wouldn't mind adding a treament on natural joins. Also, I feel the section on inner joins could include more examples of how the join attributes can be selected by the user. Anyone else agree? Lazyboyz 24 April 2006

Simple joins are missing too, and should be at the front of the article. I think this is the same thing meant by "natural joins". Hu 20:49, 2 September 2006 (UTC)

[edit] Linking To This Page

I just searched for "SQL Join" (without the quotations) and ended-up at the search page: surely this search string should be appropriate enough to consider a direct redirect to this article? 81.159.99.162 Sept. 6, 2006.

[edit] Equi join example

How does this example show anything interesting?

   SELECT e.lastname 
   FROM   employee   e, 
          department d 
   WHERE  e.departmentid = d.departmentid AND d.departmentid = 34;

since it could be replaced with

   SELECT e.lastname 
   FROM   employee   e
   WHERE  e.departmentid = 34;

to obtain the exact same output.

Shouldn't this rather show information from both tables, and _using_ the inter-relationship between them, something like this?

   SELECT e.lastname, d.departmentname
   FROM   employee   e, 
          department d 
   WHERE  e.departmentid = d.departmentid
     AND  d.departmentname = "Clerical";

Shenme 05:46, 2 October 2006 (UTC)

[edit] Usefulness of examples

How about combining a join with a sort? In business, the two most common JOINs would be:

1. Show each employee and the name of their department (alphabetical by employee name
2. Show each department and every employee in it (alphabetical by department, optionally can sort employees too)

Note that a join is used to "undo" the work of normalizing a table. Instead of maintaining a list of EmployeeName and DepartmentName, we separate the list into two tables:

• employee
  • name - text field
  • deptID - numeric link to department table (intended to be duplicated!)
• department
  • name - text field
  • ID - numeric (required to be unique!) - provides one-to-many link to employee table

Hmm, maybe the normalizing comment above should be in the article, or a related article. --Uncle Ed 16:53, 23 November 2006 (UTC)

[edit] DDL

When posting examples, things like primary and foreign keys, should be stated. But keep on working on it :-)