Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Joining a table
Message
From
13/05/2009 04:21:15
 
 
To
13/05/2009 02:03:09
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Environment versions
Visual FoxPro:
VFP 9 SP2
Miscellaneous
Thread ID:
01399545
Message ID:
01399555
Views:
76
This message has been marked as the solution to the initial question of the thread.
>Is it always necessary to do left and right join when you are joining a table? What is a better way to do this?

It depends on what you want to get with a particular SQL Select:

- Inner Join
returns only those rows that match on both sides.

- Outer Joins
Left Outer returns all rows of the left-side table, no matter if there is an equivalent on the right side regarding the On condition.
If there is no match, the right side will contain .Null. (unless you use NVL() in the field list).
Right Outer is the same vice-versa.
Full Outer Join returns all rows of both sides, with Null on the left side if there is no left-side match, and .Null. on the right side if there is no right-side match.

If you for example have a "customers" table and an "orders" table, then most likely all orders will have a match in the customers table, but not all customesr will have orders.
So you'd use an Outer Join to get all customers: customers is the left side, orders is the right side, a Left Outer makes sense:
Select * ;
From customers ; 
Left Join orders ;
    On orders.cust_id = customers.cust_id
hth
-Stefan
Previous
Reply
Map
View

Click here to load this message in the networking platform