Queries that implicit SQL joins can't do?

Question

I never found out how connections work, but just uses select and the where clause was enough for all the requests I made. Are there cases where I cannot get the correct results using the WHERE clause, and I have to use JOIN? If so, can someone please provide examples? Thanks.

Answer 1

Implicit associations are more than 20 years old. Why would you even think about writing code with them?

Yes, they can create problems that do not have explicit associations. Speaking of SQL Server, left and right joins of implicit syntaxes do not guarantee the return of correct results. Sometimes they return a cross join instead of an outer join. This is bad. This was true even before SQL Server 2000 at least, and they are phasing out, so using them is bad practice.

, -, , . , , , - . , , , , .

, -, , , , , , .

, ORM .

, , , , , , , , , , , .

SQL , .

Answer 2

. . . , ( , ) .

Answer 3

, , WHERE, JOIN?

, , . , .

WHERE, ANSI-89 JOIN. JOIN ANSI-92 , LEFT JOIN . , Oracle (+) , , SQL Server =*.

Answer 4

, , . , :

:

ID
FirstName
LastName
UserName
Password

:

ID
UserID
AddressType (residential, business, shipping, billing, etc)
Line1
Line2
City
State
Zip

, ( ), . WHERE , . , :

SELECT *
FROM Users
LEFT OUTER JOIN Addresses
    ON Users.ID = Addresses.UserID
WHERE Users.UserName = "foo"

. http://www.w3schools.com/Sql/sql_join.asp , .

Answer 5

. , , ( , oracle (-) (+) , , sqlserver * =). , , .

..........

INNER:

select a.*, b.*
from table a, table b
where a.id = b.id;

, 'a' 'b' 'id'.

:

select * from
table a LEFT OUTER JOIN table b
on a.id = b.id;

a, , 'b'. 'b' , 'b' .

, 'a', , 'b', .

, . . , , .

Answer 6

:

SELECT a.MainID, b.SubValue AS SubValue1, b.SubDesc AS SubDesc1, c.SubValue AS SubValue2, c.SubDesc AS SubDesc2
FROM MainTable AS a
LEFT JOIN SubValues AS b ON a.MainID = b.MainID AND b.SubTypeID = 1
LEFT JOIN SubValues AS c ON a.MainID = c.MainID AND b.SubTypeID = 2

Not OK, I see no way to get the same results as using the simple WHERE clause to join tables. In addition, the syntax commonly used in WHERE clauses to perform left and right joins (* = and = *) gradually stops,