CODEWITHSUNDEEP
mysqlsqlsql-serverjoin
Tilman Zuckmantel
Tilman Zuckmantel

Reputation: 720

What exactly is wrong with those SQL-Queries

i am having a little trouble with this part in my lecture over SQL-Basics. I think it is not very well explained and i am having a hard time finding any good information on the internet. In my book it says: "It is almost allways an error if two tuple attributes are not linked by an explicit join predicate: " And then this example:

SELECT s.Name, c.Name AS Contact, c.Phone
FROM Suppliers AS s, ContactPersons AS c
WHERE s.Name = 'Shop Rite'
AND c.Phone LIKE '+49 351%'

No where is explained what an explicit join predicate is. For me this example just looks fine. Beforehand there was a similair example:

SELECT s.Name, c.Name AS Contact, c.Phone
FROM Suppliers AS s, ContactPersons AS c
WHERE s.SuppID = c.SuppID

Which is an ok Join as the books says. I dont really understand what the difference is and what exactly is a JOIN-Predicate?

Also i am sorry for any grammer-mistakes (I am not a native speaker)

Thx in advance!

Upvotes: 0

Views: 58

Answers (1)

sagi
sagi

Reputation: 40491

Implicit join syntax:

SELECT * 
FROM Table1 , Table2
WHERE Table1.id = Table2.id

Explicit join syntax:

SELECT * 
FROM Table1 
JOIN Table2
 ON Table1.id = Table2.id

Implicit syntax is fine and it will work, but it's not suggested. It is confusing syntax and may lead to many mistakes , mostly when dealing with more then two tables and when you need to LEFT JOIN (the stupid plus sign) . You should make it as a habit to use only the proper syntax of a join.

Here is an example for a query with 6 tables combining LEFT JOINs :

SELECT <columns>
FROM YourTable,AnotherTable,ThirdTable,FourthTable,AnotherTable2,AnotherTable3
WHERE YourTable.id = AnotherTable.id(+) AND
      YourTable.sec_id = AnotherTable.Sec_Id(+) AND
      AnotherTable.id (+) = ThirdTable.id(+) AND
      YourTable.id = FourthTable.id AND
      FourthTable.Date = ...
      .......

As you can see, I didn't put even half of the conditions, assuming there could be a lot more conditions and it looks like crap.

Upvotes: 1

Related Questions