MySQL: Implicit Join with conditions: What kind of statement would I need for duplicate removal?

Question

I'm working on JOIN statements (implicit) and I've set up the code to join without much of a hitch, and when the code runs I get quite a few duplicates per person. I was wondering what kind of statement I should use to only show one of each person?

Select Statement

SELECT CONCAT(customers.customer_first_name, ' ', customers.customer_last_name) as 'Customer',
        orders.order_date as 'Order Date', customers.customer_zip as 'Zipcode'
FROM customers, orders, order_details
WHERE order_details.item_id = 10
ORDER BY CONCAT(customers.customer_first_name, ' ', customers.customer_last_name) ASC;

If you need other parts of the code, they are readily available.

Create Table/Insert Statements:

/*Create Tables*/
CREATE TABLE customers
(
 customer_id      INT,
 customer_first_name  VARCHAR(20),
 customer_last_name  VARCHAR(20)   NOT NULL,
 customer_address   VARCHAR(50)  NOT NULL,
 customer_city     VARCHAR(20)   NOT NULL,
 customer_state    CHAR(2)     NOT NULL,
 customer_zip     CHAR(5)   NOT NULL,
 customer_phone    CHAR(10)   NOT NULL,
 customer_fax     CHAR(10),
 CONSTRAINT customers_pk 
 PRIMARY KEY (customer_id)
);

CREATE TABLE artists
(
 artist_id      INT       NOT NULL,
 artist_name  VARCHAR(30),
 CONSTRAINT artist_pk 
 PRIMARY KEY (artist_id)
);

CREATE TABLE items
(
 item_id    INT       NOT NULL,
 title     VARCHAR(50)   NOT NULL,
 artist_id   INT   NOT NULL,
 unit_price  DECIMAL(9,2)  NOT NULL,
 CONSTRAINT items_pk 
  PRIMARY KEY (item_id),
 CONSTRAINT items_fk_artists
  FOREIGN KEY (artist_id) REFERENCES artists (artist_id)
);

CREATE TABLE employees
(
 employee_id     INT        NOT NULL,
 last_name      VARCHAR(20)    NOT NULL,
 first_name     VARCHAR(20)    NOT NULL,
  manager_id     INT,
CONSTRAINT employees_pk 
 PRIMARY KEY (employee_id),
CONSTRAINT emp_fk_mgr FOREIGN KEY (manager_id) REFERENCES employees(employee_id) 
);

CREATE TABLE orders
(
 order_id     INT     NOT NULL,
 customer_id    INT     NOT NULL,
 order_date    DATE    NOT NULL,
 shipped_date   DATE,
 employee_id    INT,
 CONSTRAINT orders_pk
  PRIMARY KEY (order_id),
 CONSTRAINT orders_fk_customers
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id),
CONSTRAINT orders_fk_employees
  FOREIGN KEY (employee_id) REFERENCES employees (employee_id)
);

CREATE TABLE order_details
(
 order_id   INT      NOT NULL,
 item_id    INT      NOT NULL,
 order_qty   INT      NOT NULL,
 CONSTRAINT order_details_pk 
  PRIMARY KEY (order_id, item_id),
 CONSTRAINT order_details_fk_orders
  FOREIGN KEY (order_id)
  REFERENCES orders (order_id),
 CONSTRAINT order_details_fk_items
  FOREIGN KEY (item_id)
  REFERENCES items (item_id)
);

/*Insert Statements*/
INSERT INTO customers VALUES 
(1,'Korah','Blanca','1555 W Lane Ave','Columbus','OH','43221','6145554435','6145553928'),
(2,'Yash','Randall','11 E Rancho Madera Rd','Madison','WI','53707','2095551205','2095552262'),
(3,'Johnathon','Millerton','60 Madison Ave','New York','NY','10010','2125554800',NULL),
(4,'Mikayla','Davis','2021 K Street Nw','Washington','DC','20006','2025555561',NULL),
(5,'Kendall','Mayte','4775 E Miami River Rd','Cleves','OH','45002','5135553043',NULL),
(6,'Kaitlin','Hostlery','3250 Spring Grove Ave','Cincinnati','OH','45225','8005551957','8005552826'),
(7,'Derek','Chaddick','9022 E Merchant Wy','Fairfield','IA','52556','5155556130',NULL),
(8,'Deborah','Davis','415 E Olive Ave','Fresno','CA','93728','5595558060',NULL),
(9,'Karina','Lacy','882 W Easton Wy','Los Angeles','CA','90084','8005557000',NULL),
(10,'Kurt','Nickalus','28210N Avenue Stanford','Valencia','CA','91355','8055550584','055556689'),
(11,'Kelsey','Eulalia','7833 N Ridge Rd','Sacramento','CA','95887','2095557500','2095551302'),
(12,'Anders','Rohansen','12345 E 67th Ave NW','Takoma Park','MD','24512','3385556772',NULL),
(13,'Thalia','Neftaly','2508 W Shaw Ave','Fresno','CA','93711','5595556245',NULL),
(14,'Gonzalo','Keeton','12 Daniel Road','Fairfield','NJ','07004','2015559742',NULL),
(15,'Ania','Irvin','1099 N Farcourt St','Orange','CA','92807','7145559000',NULL),
(16,'Dakota','Baylee','1033 NSycamore Ave.','Los Angeles','CA','90038','2135554322',NULL),
(17,'Samuel','Jacobsen','3433 E Widget Ave','Palo Alto','CA','92711','4155553434',NULL),
(18,'Justin','Javen','828 S Broadway','Tarrytown','NY','10591','8005550037',NULL),
(19,'Kyle','Marissa','789 E Mercy Ave','Phoenix','AZ','85038','9475553900',NULL),
(20,'Erick','Kaleigh','Five Lakepointe Plaza, Ste 500','Charlotte','NC','28217','7045553500',NULL),
(21,'Marvin','Quintin','2677 Industrial Circle Dr','Columbus','OH','43260','6145558600','6145557580'),
(22,'Rashad','Holbrooke','3467 W Shaw Ave #103','Fresno','CA','93711','5595558625','5595558495'),
(23,'Trisha','Anum','627 Aviation Way','Manhatttan Beach','CA','90266','3105552732',NULL),
(24,'Julian','Carson','372 San Quentin','San Francisco','CA','94161','6175550700',NULL),
(25,'Kirsten','Story','2401 Wisconsin Ave NW','Washington','DC','20559','2065559115',NULL);

INSERT INTO artists(artist_id,artist_name) VALUES 
(10,'Umani'),
(11,'The Ubernerds'),
(12,'No Rest For The Weary'),
(13,'Burt Ruggles'),
(14,'Sewed the Vest Pocket'),
(15,'Jess & Odie'),
(16,'Onn & Onn');

INSERT INTO items (item_id,title,artist_id,unit_price) VALUES 
(1,'Umami In Concert',10,17.95),
(2,'Race Car Sounds',11,13),
(3,'No Rest For The Weary',12,16.95),
(4,'More Songs About Structures and Comestibles',12,17.95),
(5,'On The Road With Burt Ruggles',13,17.5),
(6,'No Fixed Address',14,16.95),
(7,'Rude Noises',15,13),
(8,'Burt Ruggles: An Intimate Portrait',13,17.95),
(9,'Zone Out With Umami',10,16.95),
(10,'Etcetera',16,17);

INSERT INTO employees VALUES 
(1,'Smith','Cindy',null),
(2,'Jones','Elmer',1),
(3,'Simonian','Ralph',2),
(9,'Locario','Paulo',1),
(8,'Leary','Rhea',9),
(4,'Hernandez','Olivia',9),
(5,'Aaronsen','Robert',4),
(6,'Watson','Denise',8),
(7,'Hardy','Thomas',2);

INSERT INTO orders VALUES
(19,1,'2012-10-23','2012-10-28',6),
(29,8,'2012-11-05','2012-11-11',6),
(32,11,'2012-11-10','2012-11-13',NULL),
(45,2,'2012-11-25','2012-11-30',NULL),
(70,10,'2012-12-28','2013-01-07',5),
(89,22,'2013-01-20','2013-01-22',7),
(97,20,'2013-01-29','2013-02-02',5),
(118,3,'2013-02-24','2013-02-28',7),
(144,17,'2013-03-21','2013-03-29',NULL),
(158,9,'2013-04-04','2013-04-20',NULL),
(165,14,'2013-04-11','2013-04-13',NULL),
(180,24,'2013-04-25','2013-05-30',NULL),
(231,15,'2013-06-14','2013-06-22',NULL),
(242,23,'2013-06-24','2013-07-06',3),
(264,9,'2013-07-15','2013-07-18',6),
(298,18,'2013-08-18','2013-09-22',3),
(321,2,'2013-09-09','2013-10-05',6),
(381,7,'2013-11-08','2013-11-16',7),
(413,17,'2013-12-05','2014-01-11',7),
(442,5,'2013-12-28','2014-01-03',5),
(479,1,'2014-01-30','2014-03-03',3),
(491,16,'2014-02-08','2014-02-14',5),
(523,3,'2014-03-07','2014-03-15',3),
(548,2,'2014-03-22','2014-04-18',NULL),
(550,17,'2014-03-23','2014-04-03',NULL),
(601,16,'2014-04-21','2014-04-27',NULL),
(607,20,'2014-04-25','2014-05-04',NULL),
(624,2,'2014-05-04','2014-05-09',NULL),
(627,17,'2014-05-05','2014-05-10',NULL),
(630,20,'2014-05-08','2014-05-18',7),
(651,12,'2014-05-19','2014-06-02',7),
(658,12,'2014-05-23','2014-06-02',7),
(687,17,'2014-06-05','2014-06-08',NULL),
(693,9,'2014-06-07','2014-06-19',NULL),
(703,19,'2014-06-12','2014-06-19',7),
(778,13,'2014-07-12','2014-07-21',7),
(796,17,'2014-07-19','2014-07-26',5),
(800,19,'2014-07-21','2014-07-28',NULL),
(802,2,'2014-07-21','2014-07-31',NULL),
(824,1,'2014-08-01',NULL,NULL),
(827,18,'2014-08-02',NULL,NULL),
(829,9,'2014-08-02',NULL,NULL);

INSERT INTO order_details VALUES 
(381,1,1),
(601,9,1),
(442,1,1),
(523,9,1),
(630,5,1),
(778,1,1),
(693,10,1),
(118,1,1),
(264,7,1),
(607,10,1),
(624,7,1),
(658,1,1),
(800,5,1),
(158,3,1),
(321,10,1),
(687,6,1),
(827,6,1),
(144,3,1),
(479,1,2),
(630,6,2),
(796,5,1),
(97,4,1),
(601,5,1),
(800,1,1),
(29,10,1),
(70,1,1),
(165,4,1),
(180,4,1),
(231,10,1),
(413,10,1),
(491,6,1),
(607,3,1),
(651,3,1),
(703,4,1),
(802,3,1),
(824,7,2),
(829,1,1),
(550,4,1),
(796,7,1),
(693,6,1),
(29,3,1),
(32,7,1),
(242,1,1),
(298,1,1),
(479,4,1),
(548,9,1),
(627,9,1),
(778,3,1),
(19,5,1),
(89,4,1),
(242,6,1),
(264,4,1),
(550,1,1),
(693,7,3),
(824,3,1),
(829,5,1),
(829,9,1);

Yes, the table layouts would be helpful. Generally, you will have to add the key columns on which the tables join to your `where` clause or you get a cross join of all possible row combinations. — robsn, Mar 29 '16 at 23:52
I've added them, will keep it in mind in future questions. Thank you for your help. :) — Sierra, Mar 30 '16 at 00:15

score 1 · Answer 1 · edited May 23 '17 at 11:50

1

See Explicit vs implicit SQL joins ... right now you are likely getting a Cartesian product. Sorry this isn't in comments ... new user, score isn't high enough to comment yet.

edited May 23 '17 at 11:50

Community

1
1

answered Mar 29 '16 at 23:56

Andy

56
4

I don't mind at all. Thanks for the direction, I'll take a peek. :) – Sierra Mar 30 '16 at 00:04

Lajos Arpad · Accepted Answer · 2016-03-30T01:27:23.970

1

You have a Descartes product, not a join. You could use the distinct keyword or you could do a group by, but it seems you really need a join instead. I am writing something like that for you, but since I do not know your table structure, I will be guessing the columns:

SELECT CONCAT(customers.customer_first_name, ' ', customers.customer_last_name) as 'Customer',
        orders.order_date as 'Order Date', customers.customer_zip as 'Zipcode'
FROM order_details
join orders
on order_details.order_id = orders.order_id and order_details.item_id = 10
join customers
on orders.customer_id = customers.customer_id
ORDER BY CONCAT(customers.customer_first_name, ' ', customers.customer_last_name) ASC;

Naturally, there is no guarantee there will be a single record per customer, since we, at least lacking information cannot assume that there are no customers who have multiple orders, each having an order detail with item_id = 10

edited Mar 30 '16 at 01:27

answered Mar 30 '16 at 00:04

Lajos Arpad

64,414
37
100
175

Thank you very much for the assistance, but I'm afraid thats only given more questions...So, that's not an implicit join. It was my understanding that an implicit join was in such a form that you don't use the JOIN keyword. But if not, what would an implicit join be? Is that what actually makes this a Descartes product (or Cartesian)? – Sierra Mar 30 '16 at 00:13
Oh, but thank you all the same! This has been very insightful and I've gotten it figured out. It certainly looks more appropriate than it did before. :) – Sierra Mar 30 '16 at 00:20
Wait, I get the part about the form of implicit now. Just figured it out. – Sierra Mar 30 '16 at 00:24
@SierraMcGivney, you were doing Descartes product, also called implicit join, but, if we look at the meet/join operations defined in the algebraic structure called "web", then join is a common multiplicator of the elements, in this case, relations, or in other name, tables. However, since you have the filter in the where clause, which is (at least theoretically, but probably practically as well) executed after all the records found a common multiplicator (join), the filter reduces it. – Lajos Arpad Mar 30 '16 at 01:23
When you use joins, the filters in the on clauses are executed while the from is being defined, excluding excludable record pairings while doing so. The difference is that in Descartes products (which is deprecated since 1992) you load all the pairings (useful and useless) alike into memory, taking more time and allocating more space than needed and then, on the large, multidimensional data set the filter is issued. On the other hand, joins are executing the on clause, which makes this quicker and your database will scale better as a result, hence the deprecation of "implicit join". – Lajos Arpad Mar 30 '16 at 01:26
Sierra, I have edited my answer, removed the where clause and fixed the column names according to the database scheme you have shared with us in your question. – Lajos Arpad Mar 30 '16 at 01:28
1

That makes sense, and it answers one of my unvoiced questions. Thank you for the thorough explanation and for updating your answer. You have been quite helpful and it is very much appreciated. :) – Sierra Mar 30 '16 at 22:16

score 1 · Answer 3 · answered Mar 30 '16 at 00:23

1

If you don't want to use the JOIN keyword you can add the key columns on whick the tables are related to the WHERE clause like this:

SELECT CONCAT(customers.customer_first_name, ' ', customers.customer_last_name) as 'Customer',
        orders.order_date as 'Order Date', customers.customer_zip as 'Zipcode'
FROM customers, orders, order_details
WHERE order_details.item_id = 10
AND orders.customer_id = customers.customer_id
AND order_details.order_id = orders.order_id
ORDER BY CONCAT(customers.customer_first_name, ' ', customers.customer_last_name) ASC;

answered Mar 30 '16 at 00:23

robsn

734
5
18

THIS is very helpful!! You've definitely helped me to realize I'd been doing something wrong this whole time. Thank you very much!!! :) – Sierra Mar 30 '16 at 00:28
While this answer is correct, we should mention that this is inferior to using the join keyword. ON is executed while the tables are being joined, sparing memory and time compared to Descartes multiplication. – Lajos Arpad Mar 30 '16 at 01:29
@LajosArpad Possibly. We'd have to compare the execution plans. This has been asked [here](http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) and there was no difference. I didn't test it myself though. – robsn Mar 30 '16 at 01:34

MySQL: Implicit Join with conditions: What kind of statement would I need for duplicate removal?

3 Answers3