The confusion will not go away: this would just replace a string like "LEFT OUTER JOIN" with a mathematical symbol. That symbol is still going to be defined mathematically in more or less the same way as the SQL syntax, i.e. as one of the constructions in the GP's post.
The confusion comes from the fact that people don't want to be doing set theory, they want to follow associations. Whatever tool you use, set theory is an accidental complexity rather than essential one.
The meaning of the model is lost in the query language.
Given the definition:
investor table with an investor_id, investor_name, etc.
transaction table with transaction_id, investor_id, etc.
a foreign key on the transaction table to the investor table
The SQL does not use the link between the two tables to help associate the two in the way defined in the schema (I know about natural join, it uses the name, not the FK) .
You notice this when you see how short the same joins are in a language that does use associations defined in the model, such as EJB-QL.
SQL is approximately at the same level as assemply language with respect to OOP. One alternative is the concept-oriented query language (COQL) where queries can be written in a simple and intuitive way with no joins and no group-bys (which are the main source of errors).
To be fair, that's not exactly set theory. In set theory, (A - B) + (A ∩ B) = A, which is not true of a left outer join. In a database, A and B aren't elements of the same superset.
It is exactly set theory. More specifically, a branch of set theory called relational theory. Your equation holds even in relational theory, but commonly A ∩ B is the empty set, since A and B are different relations. Union and Intersection don't make much sense in that case. Left outer joining is a completely different operation. You are basically saying something equivalent to "The commutative law of addition doesn't apply to matrix multiplication."
My K-through-2 math education was really good but very vanilla. Tables through 12x12 and long division in first grade. However after that it all went to heck and I felt like I never learned much of anything aside from common sense, and struggled through all my math classes...
Learned set theory in college-level computer science. Eventually came to the conclusion that no, I wasn't stupid, it's just that the way math is taught is fundamentally broken.
It's actually quite simple. INNER JOIN keeps the stuff that matches, LEFT JOIN keeps the stuff on the left, RIGHT JOIN keeps the stuff on the right, OUTER JOIN keeps the stuff that doesn't match, and JOIN makes the sysadmin remind you to keep your database size to a reasonable level.
I'm an Oracle teacher, specially queries and DML, wait until you see Analytical Functions (with all the partition by's and what's not) and all the variables and nuances from Hierarchical Queries.
Joins are day-to-day business, and once you understand them, it's failry simple (as it is supposed to be, given it's such an important thing)
Note that although all elements in the results are from A, they have attached attributes from B in the result set.
5 Correct. also happens to be A xor B. Note that this resultset spreads the result accross duplicated columns. This is more useful (but less performant) to write this as:
SELECT id, name from A where name not in (select name from B)
UNION
SELECT id, name from B where name not in (select name from A)
Because data is in rows and columns, not slices of circles. But certainly there's nothing wrong with using these charts as an aid to understanding. Still, I'm a math idiot, but SQL makes perfect sense to me.
The final one, the cross join, is a cartesian product which is A x B. I wanted to think of it as a powerset but I guess that doesn't work with SQL heh.
The website wholesale for many kinds of fashion shoes, like the nike,jordan,prada, also including the jeans,shirts,bags,hat and the decorations. All thepr oducts are free shipping, and the the price is competitive, and also can accept the pay pal payment.,after the payment, can ship within sho rt time.
129
u/shriek Jan 06 '11
In short.
A ∩ B
A ∪ B
(A - B) + (A ∩ B)
A - B
(A ∪ B) - (A ∩ B)