How can I join 3 tables in SQL using outer joins?

Question

So I am trying to Join these three tables in order to List the names and prices of products provided by a particular company.

I have tried using Natural joins but I do not seem to be getting the right output. (I am expecting 3 rows to be returned but I am getting more than that)

All the answers here suggests that I use a Inner join but I do not want to get all the combination of rows

Thanks

Edit:

The query I wrote:

SELECT Pieces.Name, Provides.Price
FROM Pieces
JOIN Provides JOIN Providers
WHERE Providers.Name LIKE "Honda";

With the query above I get no results

The picture below shows the result I am expecting

Can we see any of your existing query? You say you expect 3 rows, but why is that specifically? To be honest this looks like an inner join situation, unless you're trying to include pieces that don't have any providers, or providers that don't actually provide anything — Marc Gravell, Dec 14 '17 at 00:03
Hi. `inner join` is `cross join` rows (all combinations) restricted to a condition given in `on`. `join` with no `inner` is `inner join`. `where` is like an `on` that comes after all `join`s. MySQL allows [`inner`] `join` without `on`, which just means `cross join`. Plus that's a *description of the result*, not a prescription for implementation/execution. That is how we write queries. "I don't want all combinations" is misconceived. PS "I tried" & "I expected" tell us nothing; I guess "no results" means "a result with no rows". Be clear. Show what happened. (As with your examples.) [mcve] — philipxy, Dec 14 '17 at 06:56
Re `natural join` see [this](https://stackoverflow.com/a/46558304/3404097). — philipxy, Dec 14 '17 at 07:05

Caius Jard · Accepted Answer · 2017-12-14T08:52:20.560

I'd like to talk a little about joins, with the aim of drawing a more clear distinction in your mind of how they work:

INNER JOINs do not result in "all combinations of rows" - that's what a CROSS JOIN does

OUTER JOINs are useful where you have one table with all the values(the solid table), and you need to join it to other tables that don't necessarily have any row that is related to the solid table - these are sparse tables (holes in the data). Outer joins come in 3 flavours: left, right and full. Left and right are the same thing, you just pick the appropriate keyword depending on the order of your tables. Essentially a left join b and b right join a amount to the same thing - the keyword left/right declares which table you consider to be solid and which you consider to be sparse.

An inner join of a solid and sparse table pair would eliminate some solid rows in the results leaving only the sparse rows and the related solid rows. An outer join would represent all solid rows, and empty cells where no matching sparse row was found

Natural JOINs are JOINs where you don't explicitly state how the tables are related, you just let the database guess based on equal column names in both tables. Don't use natural JOINs, theyre nearly always a stupid idea and a pointless "optimisation" that saves a few seconds of keystrokes in exchange for hours of frustrating debugging

Your query here doesn't seem to require outer JOINs, as you state you want to know which pieces are provided by which company.. that's just

SELECT c.* FROM
  Provider r 
  JOIN Provides s ON r.code = s.provider
  JOIN Pieces c ON c.code = s.piece
WHERE 
  R.Name = 'Mattel'

Edit: Philipxy has made a reasonable point that the keywords "solid" and "sparse" have particular meanings in maths, and I'd like to point out that I'm using them here to convey a concept rather than drag that particular aspect of maths into it. As you're looking for understanding, I thought I'd discuss things in this way because I've classically found it an easy way for learners to grasp the concepts, but these are essentially my own terms for referring to the shape of the database data you're working with and I don't necessarily guarantee that a future fellow professional who is heavily invested in his understanding of mathematics will be able to grasp the meaning of what you're saying, if you use keywords that he only uses/understands in a particular way. In that case you may need to add a similar disclaimer if you're using "solid" and "sparse" that you simply mean them in the generally accepted English Language meanings, rather than the mathematical sense

Thanks for the help and for the explanation.. SQL JOINS were confusing to me but with that explanation all of the confusions are gone — aspire29, Dec 14 '17 at 00:30
You might like this blog post - https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ — Caius Jard, Dec 14 '17 at 00:36
@aspire29 & CaiusJard That link is a mess. See my comments & answer at [What is the difference between “INNER JOIN” and “OUTER JOIN”?](https://stackoverflow.com/q/38549/3404097) and [CROSS JOIN vs INNER JOIN in SQL Server 2008](https://stackoverflow.com/q/17759687/3404097). — philipxy, Dec 14 '17 at 06:42
@aspire29 & CaiusJard Solid/sparse is misleading. Normally for matrices dense/sparse means few/most n-dimensional points map to 0. Relationally that's *like* an (n+1)-column table with an n-column key & all possible key values though most/few rows, or a 1-dimensional matrix/array/vector of most/few of the possible multidimensional points with given columns. The dense/sparse proposed here mean *relatively* & *typically* since *any* 2 tables can be joined on *any* columns/condition. The normal terms are inner/outer tables for the left/right ones in a left join & right/left ones in a right join. — philipxy, Dec 14 '17 at 06:53

How can I join 3 tables in SQL using outer joins?

1 Answers1