CODEWITHSUNDEEP
mysqlsqlselectnatural-join
jor
jor

Reputation: 843

MySQL NATURAL JOIN 3 tables related to a parent table with a contraint on the parent table

I have 3 child tables that are related to a parent table with the following schema:

Product(maker, model, type)
PC(model, price, ...)
Laptop(model, price, ...)
Printer(model, price, ...)

The Product table is the parent table of PC, Laptop, and Printer. The three child tables have a foreign key model that reference the Prodcut table. Also, PC, Laptop, and Printer don't have identical schemas.

I want to find out the best way to SELECT the model and price of all the products(pc, laptop, and printer) that are made by a certain maker.

I'm new to SQL and my intuition right now tells me that I should join the parent table to each of the child tables, filter the results, and then perform a union. The query appears to return the correct result.

Here is my query:

SELECT model, price FROM Product NATURAL JOIN PC WHERE maker = 'B' UNION 
SELECT model, price FROM Product NATURAL JOIN Laptop WHERE maker = 'B' UNION 
SELECT model, price FROM Product NATURAL JOIN Printer WHERE maker = 'B';

Is this an effective way to query for my information? I feel like I'm typing way too much and repeating much of the same constraints to achieve my result.

Is there a better query I could write?

Thanks.

Upvotes: 0

Views: 1332

Answers (2)

Abhishek Gupta
Abhishek Gupta

Reputation: 4166

Well to increase the efiiciency use UNION ALL rather than UNION (or UNION DISTINCT)

SELECT model, price FROM Product NATURAL JOIN PC WHERE maker = 'B' UNION ALL
SELECT model, price FROM Product NATURAL JOIN Laptop WHERE maker = 'B' UNION ALL
SELECT model, price FROM Product NATURAL JOIN Printer WHERE maker = 'B';

The difference in execution speed comes from the fact UNION requires internal temporary table with index (to skip duplicate rows) while UNION ALL will create table without such index.

Even to know more, please go through the article http://www.percona.com/blog/2007/10/05/union-vs-union-all-performance/

Upvotes: 1

artm
artm

Reputation: 8584

I'm not sure about the MySql but something like this?

SELECT model, price 
FROM Product 
JOIN PC 
ON PC.model = Product.model
JOIN Laptop
ON Laptop.model = Product.Model
JOIN Printer 
ON Printer.model = Product.model
WHERE Product.maker = 'B'

Edit 1: Try this:

SELECT p.model, p.price 
FROM Product p
JOIN PC pc
ON pc.model = p.model
JOIN Laptop lp
ON lp.model = p.Model
JOIN Printer pr
ON pr.model = p.model
WHERE p.maker = 'B'

Upvotes: 1

Related Questions