MySQL has no outer join. Why isn't this fact all over the internet? Why does EVERY FUCKING TUTORIAL list outer join as if it's just as standard as left join?

Eh.

Left / Right / Cartesian?

Fully supported...

Or what am I missing here?

@IntrusionCM full join / full outer join isn't supported.

@homo-lorens it is.

https://dev.mysql.com/doc/refman/...

Cross join should be what you are looking for.

Although a cartesian product is - usually - a bad idea TM.

Meep meep mother fucker said the madness to the brain.

Full outer join would be a non distinct UNION from a left join and a right join select.

Full join the same with an INNER and a left join.

(meep meep mother fucker was a monologue to myself by the way. Had nothing to do with you)

Isn't LEFT OUTER JOIN the same as LEFT JOIN with an extra condition?

I think it was something like this:
- LEFT JOIN:
select * from a left join b on a.key = b.key
- LEFT OUTER JOIN:
select * from a left join b on a.key = b.key and b.key is null

I haven't used mysql in so long... I didn't realize this either what are you supposed to do use their lack of table variables to pull rows matching and right a million subqueries for included columns from other tables ?

@MadMadMadMrMim A full outer join is not natively supported, that's right.

But it is... An uncommon pattern… imho.

MySQL supports virtual columns, temporary / in memory and persisted.

And the rest ... I don't understand your comment. It reads like gibberish?

(Sorry for my gibberish earlier, brain was fried and I could not think straight)

@IntrusionCM isn’t that just a left or right join ? You know always include this result set even if no matching rows are found on the join condition ?

@IntrusionCM oh I meant if you can’t left join for example what are you supposed to do to gain the same effect ? Write subqueries for the columns that would have been joined or populate a temporary table with nulls in the case of nonexistent rows and key them with the original query results join ids ?

@MadMadMadMrMim No.

A full outer join is the result of the union of a left join and a right join. Hence you can use a union of two selects as an alternative.

Left Join / Right Join / Inner Join / Cross Join are supported.

@IntrusionCM why not just call it a union ? And doesn’t that just append the rows from one query to another ? Confusing jargon !

@MadMadMadMrMim :) confusing jargon... I guess that's true for a lot of stuff in programming.

And I think you still didn't get the full thing.

FULL OUTER JOIN equals
(SELECT * FROM A LEFT JOIN B ON ...)
UNION ALL
(SELECT * FROM A RIGHT JOIN B ON ...)

As I said - the pattern is rather unusual.

But you have now the result of _each_ relation (relation of a to b and b to a).

Usually you know just one relation - how a relates to b.

The word "outer" might be confusing, and is left out in many SQL dialects.

Left Join and Left Outer Join are the same

@IntrusionCM inflection was meant to be humorous but yeah I would never have inferred that meaning from the terminology.

Why would one do this ?
It looks like it would create some duplicate rows.

@MadMadMadMrMim exactly.

And that's the point.

Let's say A contains words, B contains categories.

A full outer join would give you a list of all words with their categories (null if non existent) - left join. But it would miss if a _category_ had no words.

Through the union all and the right join, this gets added to the result set, too.

So you now see the relation words to category and category to words.

If words without categories exist, NULL.
If categories without words exist, NULL.

Looks like it would pull back duplicate rows where an inner join would work too though unless I misread something

@MadMadMadMrMim Union deduplicates rows where all fields match.

@homo-lorens nuance I never had to consider hehe my queries never contained duplicates when I used union in rare instances

@homo-lorens Yiu know what would be nice ? If in some cases where you create fields in queries by combing other values and logic expressions if small differences in type could be detected by the database engine and not require casts just choosing whichever return type for a field that was the same major data type had the greater length of precision

@MadMadMadMrMim If you extend the precision of floats their meaning could change depending on when exactly you do it. But for int and others I agree, extending Precision should be default behavior.

@homo-lorens good point on floats given comparison is usually based on truncated precision of actual values