Intersect and Except

Not all database platforms support INTERSECT and EXCEPT (MINUS in Oracle), and MySQL is one of them. Let’s try to solve two simple related problems:

  • We want department numbers that appear in both Departments and Employees tables. In other words, we’re looking for all departments with at least one employee. This is a great candidate for INTERSECT, because we want numbers that exist in both tables.
  • We also want all department numbers that do not appear in Employees. In other words, we’re looking for departments with no employees. This is a great candidate for EXCEPT, because we want numbers that exist in only one table.

(more…)

Fastest way to build vendor-independent queries

Prepare to be amazed. Really.

What do you know about grouping sets? Not much?…

Let’s see how hard it is to build this query first. From the Databases tab, on the left, I right-click on the emp table and select Add to New Query. I keep checked only the DEPTNO, JOB and SAL shape items. I show only the Field, Lookup, Group, Sort and Format builder bars. I click on Page Size to turn paging off. (more…)

Left or Right Join? Should I Care?

Here is why Query Xtractor users may call them LEFT/RIGHT JOIN, without being specific. You could not always tell if a diagram shape is at the left of the other joined shape. And you should not actually care, this is a SQL implementation detail. Leave it to us, to our SQL query generator, to determine if this will translate into a “a LEFT JOIN b” or “b RIGHT JOIN a”.

Better than a Spreadsheet

Grouping is great, but it should be just the beginning. How many times you had some aggregates (totals, counts etc), but could not immediately and instantly expand ANY row, to see what records have been actually considered together? In Query Xtractor, result rows of any GROUP BY query are designed to be further navigated this way!