Cartesian products, outer joins, and self joins explained. (Structured Query Language) (Tutorial)

Structured Query Language (SQL) can be a bit intimidating when you start to learn it. Not only must you learn proper SQL syntax, but you must also learn some of the terms used to describe set operations. As you may know, most client/server database management systems are set oriented and use SQL syntax for data manipulation. In this month's column, I'll explain three terms that are commonly associated with SQL data manipulation: Cartesian products, outer join, and self join.

Why should you care these terms? Cartesian products should be understood so they can be avoided. If you're not using outer joins, you may be doing a lot of extra work that wouldn't be necessary if you used an outer join. In addition, without an outer join you could inadvertently be excluding relevant data. Self joins are an alternative to subqueries. As you become more fluent in your SQL use, you'll learn that it's good to have many tricks up your sleeve when optimizing SQL statements. Sometimes a slight change in syntax can make a significant difference in performance.

Cartesian products

A Cartesian product is the set of all possible rows that could result from joining …