union will deduplicate. Use
union all to keep the duplicates.
Any sample code you tried to substantiate this statement? Talking about concepts is good for idea exploration, but staying in that space, holding on to more and more ideas without getting to work proving or disproving them will serve to confuse.
Try setting up your own small database and tables on sqlfiddle.com or sqliteonline.com, and create your own union and join operations to see the effects.
Here is a simple explanation with visuals: https://www.essentialsql.com/what-is-the-difference-between-a-join-and-a-union/. (this website is really good explaining other concepts too) You can copy paste small code snippets from online to set up simple databases to do your experiments. Here are some experiments i have done, you can see how to set up tables from them:
Window Functions vs DISTINCT vs GROUP BY relationship
Postgres SQL GROUP BY refering to column in SELECT?
SQL summing down columns and across rows, and COALESCE
PIVOTING in SQL - Comparing CASE vs JOIN
Here too you can find examples of how other’s set up data to prove a feature: https://www.sql-workbench.eu/dbms_comparison.html
UNION and JOIN are totally different things, so cannot replace each other’s functions, but if used as part of a solution to solve a problem, they can contribute in different ways to the same result.
Replacing JOIN with UNION: https://dba.stackexchange.com/questions/168699/can-i-replace-these-left-joins-with-a-union-or-some-other-optimization
In this example, the desired result is to find whether every object is in group A and/or group B.
The UNION method (2nd solution) is basically hardcoding the 1 and 0 created from IS NULL in the 1st solution. Also the 1st solution begins join from
object table, the 2nd solution joins in the
object table last.
Replacing UNION with JOIN: https://stackoverflow.com/questions/10157468/sql-query-replace-union-all-with-joins-or-something
In the JOIN method, the
OrderID = O.ID are used for alignment when Date, Machine columns are removed from
BadPieces tables compared to the UNION method. In the sqlfiddle demo, you don’t have to use the exact
OrderID selected in that example, as long as the Date,Machine matches, you will get same results.
insert GoodPieces (OrderID, Pieces) values
can be replaced with