You might be thinking that doing a full table scan sounds inefficient for something so simple – shouldn’t software be smarter?
It’s almost like looking through the entire table with the human eye – very slow and not at all sleek. But, as you probably guessed by the title of this article, this is where indexes … Read More →
You have a sales table listing product, salesperson and amount:
DROP TABLE IF EXISTS sales;
CREATE TABLE sales (
id int(11) default NULL,
product char(5) default NULL,
salesperson char(5) default NULL,
amount decimal(10,2) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO sales VALUES
SELECT * FROM sales;
| id | product | salesperson | amount |
| 1 | radio | bob | 100.00 |
| 2 … Read More →
Have you ever designed an ER Diagram in MySQL Workbench and wondered, just what is the difference between “Identifying” and “Non-Identifying” relationships? Non-Identifying Relationships have dotted lines, whereas Identifying Relationships have solid lines in MySQL Workbench. The difference is subtle but worth knowing.
When you create an identifying relationship, the primary key of the child table … Read More →
MySQL implements UNION, but does not directly implement INTERSECTION or DIFFERENCE.
INTERSECTION is just an INNER JOIN on all columns:
drop table if exists a,b;
create table a(i int,j int);
create table b like a;
insert into a values(1,1),(2,2);
insert into b values(1,1),(3,3);
select * from a join b using(i,j);
| i | j |
| 1 | 1 … Read More →
OUTER JOIN queries in SQL are susceptible to two very subtle bugs that I’ve observed a number of times in the real world. Daniel and I have been hammering out ways to automatically detect queries that suffer from these bugs, in a relatively new Maatkit tool called mk-query-advisor. It’s part of our series of advisor … Read More →
There is usually more than one way to write a given query, but not all ways are created equal. Some mathematically equivalent queries can have drastically different performance. This article examines one of the motivations for inventing LEFT OUTER join and including it in the SQL standard: improved performance through exclusion joins.
LEFT OUTER join syntax … Read More →
Here’s a way to write join clauses so they are more compact, more readable, and less confusing.
Standard SQL:2003 defines a USING clause that can serve the same function as the ON clause in the familiar JOIN syntax. For example, the following join
select a.col1, b.col2
inner join b on a.col3 = b.col3
May be … Read More →
In this article I’ll show several ways to emulate a FULL OUTER join on a RDBMS that doesn’t support it, as is the case with even the most recent versions of MySQL. This useful query is surprisingly tricky to get right.
A standard SQL FULL OUTER join is like a LEFT or RIGHT join, except that … Read More →
from the Artful Common Queries page
There are two main ways to reconcile payments against charges:
Open Item: match payments against individual charges, typically by carrying the charge number in the payments table
Statement: list and sum all charges and all payments, and show the difference as the outstanding balance.
The Open Item method needs a foolproof way … Read More →
Parents without children
Given tables parent(id INT), child(id INT,parent_id INT), how do we find parents with no children? It's the All X for which there is no Y pattern, which can be written as an exclusion join...
LEFT JOIN child ON parent.id = child.parent_id
WHERE child.parent_id IS NULL;
or with a NOT EXISTS subquery, which is logically equivalent to the exclusion join, but usually performs much slower:
SELECT parent.id AS ParentID
WHERE NOT EXISTS (
JOIN child ON parent.ID = child.parent_id
Parties who … Read More →