13 ms·
Let's talk about joins
- dcastm 3y ago[flagged]
- adrianmonk 3y agoFrom the opening of the "Vertical Joins" section: > Similar to horizontal joins, there are many use cases for joining data horizontally, also called appending data. Shouldn't this read "joining data vertically"? This seems like a typo.
- deleted 3y ago[deleted]
- cghlewis 3y agoThis is definitely a typo! Thanks for catching this!
- jethkl 3y agoThe article omits the "natural join". A natural join automatically joins tables on columns having the same name, and the columns in the resulting output appear exactly once. Natural joins often simplify queries, and they automatically adapt to schema changes in source tables. Natural joins also encourage standardized naming conventions, which has longer-term benefits.
- youerbt 3y agoI feel like natural joins simplify writing queries, but not exactly reading them (especially if you are not familiar with the database). IMO a good compromise is the USING clause, which acts like a natural join, but columns have to be named explicitly.
- hipadev23 3y agoThat’s not a join type, that’s syntactic sugar.
- bradleybuda 3y agoA right join is also syntactic sugar for a left join
- bakuninsbart 3y agoExactly, that's why they shouldn't be used. If you feel the need to use a right join, swap the direction around. And only use inner joins if it is the only type of join you use, otherwise specify conditions in the where clause. Both serve to significantly lower mental overhead when dealing with queries. Natural joins are naturally more implicit, and while SQL tends to be a little bit verbose, given the significance of data integrity and the dififculty of testing SQL, the trade-off goes very clearly towards being explicit.
- cldellow 3y agoNatural joins also automatically break your queries when two columns happen to share a name but not the same meaning. Step 1: use natural join. Life is great. Step 2: someone adds a `comment` field on table A. Life is great. Step 3: someone adds a `comment` field on table B. Ruh roh. I'll use them in short-lived personal projects, but not on something where I'm collaborating with other people on software that evolves over several years.
- jethkl 3y agoa defense against collisions like this is through CTEs that select a minimal set of columns, with column names suitably selected and standardized: CTE_A AS (SELECT ... comment as comment_a from A...), CTE_B AS (SELECT ... comment as comment_b from B...)
- Cyberdog 3y agoIsn't this a lot more work both for the user and the RDBMS than just using a relatively simple left join?
- closeparen 3y agoIt seems like the database should be able to figure this out when a foreign key constraint is explicitly declared in the DDL.
- tqi 3y agoShould, but in practice I've rarely seen fk contraints used in analytics data warehouses (mostly for etl performance reasons)
- civilized 3y ago> Natural joins also encourage standardized naming conventions, which has longer-term benefits. This is a very positive spin on "you have to manage column names very rigorously for this strategy to be sustainable".
- layer8 3y ago> the columns in the resulting output appear exactly once No, records with the same join-column value are multiplied.
- mgaunard 3y agoThere are lots of other joins not mentioned there. A popular one with time series is the asof join. There are also literal joins, which are generalizations of adjacent difference.
- mgaunard 3y agomeant lAteral sorry
- moon_moon 3y ago[dead]
- HermitX 3y agoExcellent learning material, thanks for sharing. I've noticed an interesting trend: JOINS are crucial for data analytics, yet many new open-source data analytics products, or open-source OLAP products, offer limited support for JOINS. Examples include ClickHouse, Apache Druid, and Apache Pinot. It seems that currently, only Trino and StarRocks provide robust support for JOINS. Commercial products tend to have better support in this area. I hope the various open-source projects will also enhance their JOIN capabilities.
- minitoar 3y agoThe reason those tools have more limited support for joins is mainly because they are making intentional trade offs in favor of other features, eg performance in a particular domain.
- totalhack 3y agoI've also been frustrated when testing out tools that kinda keep you locked into one predetermined view, table, or set of tables at a time. I made a semantic data modeling library that puts together queries (and of course joins) for you as it uses a drill-across querying technique, and can also join data across different data sources in a secondary execution layer. https://github.com/totalhack/zillion https://github.com/totalhack/zillion Disclaimer: this project is currently a one man show, though I use it in production at my own company.
- khusmann 3y agozillion looks fantastic. I've wanted for a long time to build something similar for warehousing the education and social science research data I work with, but have found it difficult to spend time building infrastructure as someone currently spending most of their time in academia. What does your company do, if you don't mind me asking? Any interest in expanding into research data management? I'd love to chat with you sometime if you're at all interested... (my contact is in my profile)
- totalhack 3y ago
- tosh 3y agoasof join: https://code.kx.com/q/ref/asof/ https://code.kx.com/q/ref/asof/ https://code.kx.com/q/learn/brief-introduction/#time-joins https://code.kx.com/q/learn/brief-introduction/#time-joins https://clickhouse.com/docs/en/sql-reference/statements/select/join#asof-join-usage https://clickhouse.com/docs/en/sql-reference/statements/sele... https://duckdb.org/docs/guides/sql_features/asof_join.html https://duckdb.org/docs/guides/sql_features/asof_join.html > Do you have time series data that you want to join, but the timestamps don’t quite match? Or do you want to look up a value that changes over time using the times in another table? DuckDB blog post on temporal joins: https://duckdb.org/2023/09/15/asof-joins-fuzzy-temporal-lookups https://duckdb.org/2023/09/15/asof-joins-fuzzy-temporal-look... note: this idea is very useful for timestamps but can also be used for other types of values
- jamesblonde 3y agoThe ASOF LEFT JOIN is the main join used to create training data, where the labels are in the left table, and features are in the tables on the RHS. Note, we use DuckDB for its left asof join capabilities, but most vendors use Spark which is hideously slow for left asof joins.
- andy81 3y agoIf I was going to add anything - "Vertical join" is normally called union. "Right join" is just (bad) syntax sugar and should be avoided. Left join with the tables reversed is the usual convention. The join condition for inner is optional- if it's always true then you get a "cross join". Can be useful to show all the possible combinations of two fields.
- wood_spirit 3y agoAll the other non-left joins are just syntactic sugar and can be expressed using only left join…?
- tzot 3y ago> All the other non-left joins are just syntactic sugar and can be expressed using only left join…? A “vertical join” (SQL UNION) is one of the “non-left joins”. How can you transform a “vertical join” to a “left join”?
- cbreezyyall 3y agoThis feels like an interesting interview question. I think you could simulate this with a full outer join on the entire select list and coalesce? For a UNION ALL you could put some literal column in the selects from both tables that you set to different values and include that in the join so you'd get a result set that will have all nulls in the right table columns for the rows in the left table and vice versa. Something like WITH top_t AS ( SELECT a ,b ,c ,'top' as nonexistent_col FROM table_1 ), bottom_t AS ( SELECT a ,b ,c ,'bottom' as nonexistent_col FROM table_2 ) SELECT COALESCE(top_t.a, bottom_t.a) AS a ,COALESCE(top_t.b, bottom_t.b) AS b ,COALESCE(top_t.c, bottom_t.c) AS c FROM top_t FULL OUTER JOIN bottom_t ON top_t.a = bottom_t.a AND top_t.b = bottom_t.b AND top_t.c = bottom_t.c AND top_t.nonexistent_col = bottom_t.nonexistent_col -- remove this for a normal UNION
- wood_spirit 3y ago
- nikhilsimha 3y agoNever heard a union be called a “vertical join” before.
- airstrike 3y ago> Let’s try this again using R. This should actually be "Let's try this again using dplyr, one of the most elegant pieces of software ever written". Hadley Wickham is a treasure to humanity.
- jarym 3y ago> “Here we typically expect that the combined dataset will have the same number of rows as our original left side dataset.” For left join this isn’t entirely true. If there are more matching cases on the right joined table then you’ll get additional rows for each match. That is unless you take steps to ensure only at most one row is matched per row on the left (eg using something like DISTINCT ON in Postgres)
- wood_spirit 3y agoYes I picked that up and was tempted to comment. But then half way down it addresses this with the section “Many relationships”: > Until now we have discussed scenarios that are considered one-to-one merges. In these cases, we only expect one participant in a dataset to be joined to one instance of that same participant in the other dataset. > However, there are scenarios where this will not be the case…
- jarym 3y agoGot it, in my opinion the structure is likely to mislead people because it doesn’t make clear that it refers to ‘one-to-one’ relationships at the outset (in fact not even, it deals with the cases of one-to-one and one-to-none) - it only refers to ‘left join’.
- wood_spirit 3y agoYeap, the whole article is confusing to us who are already very familiar with eg SQL joins and things. It’s not using mainstream terminology. But I guess we are not the audience.
- HelloNurse 3y agoI agree that this article is introductory, but it makes the many non-standard and anti-standard terms (e.g. "vertical joins") less forgivable: misleading learners is worse than irritating experts.
- 3y ago
- zzzeek 3y agowhen I saw the term "horizontal joins", I immediately went to, what? what's a "vertical join?" must be a UNION, scrolled down and sure enough. Is there some reason to use non-standard terminology for posts that are trying to be in-depth, authoritative tutorials ?
- genman 3y agoGood material. Joins can be also categorized by the used join algorithm. The simplest join is a nested loop join. In this case a larger dataset is iterated and another small dataset is combined using a simple key lookup. Then there is a merge join. In this case two larger datasets are first sorted using merge sort and then aligned linearly. Then there is a hash join. For the hash join a hash table is generated first based on the smaller dataset and the larger dataset is iterated and the join is made by making a hash lookup to the generated hash table. The difference between nested loop join and hash join might be confusing. In case of a nested loop join the second table is not loaded from the storage medium first, instead an index is used to lookup the location of the records. This has O(log n) complexity for each lookup. For hash join the table is loaded and hash table is generated. In this case each lookup has O(1) complexity but creation of hash table is expensive (it has O(n) complexity) and is only worth the cost when the dataset is relatively large.
- tmoertel 3y agoIn distributed systems, there's even a broadcast hash join, in which the hash table is not distributed across shards and assigned to workers but copied in full to every worker. That way, the other side of the join need not be sharded and shuffled to align join keys across workers. This strategy can save a lot of time and network bandwidth when one side of the join is small enough to fit into a worker's RAM and the other side is staggeringly massive (e.g., logs). It lets the massive side be processed in place, as it's streamed from storage.
- lakomen 3y agoBeginners talking about SQL joins. What is this newbienews or hackernews? "But but ChatGPT says"... Disgusting
- rvba 3y agoSomeone even coined the term vertical join! Anyway, I declare and coin that VLOOKUP is a LEFT JOIN
- brodo 3y agoI like reading well-written articles about basic things, even as a senior dev. My job involves mentoring people, and it is always good to have some articles at hand to give them to read if they struggle with a topic. Moreover, it is interesting to see how the concepts are thought. I like to give people several explanations for the same thing in the hope that at least one sticks.
- holoduke 3y agoIts funny that the older i get 40 the more it seems like my surrounding doesn't even understand the basics of sql. In my 20s everyone knew sql. It was required for almost every job. And it is still very useful knowledge. Difference between left, right and inner joins is ultra basic. If you dont know that i would be very worried.
- dan-robertson 3y agoThis article is targeted at people using R to analyse data which is probably more as-hoc and not in a sql database. I don’t think the target audience (might include eg scientists who are working with experiment results) should be expected to know SQL, and even if they do, it’s still useful to understand the dplyr functions,
- brodo 3y agoI see more and more people coming out of college (or through apprenticeships) with miserable SQL skills. Relational databases are boring technology, and people want to work with shiny new things. People don't want to learn it, and they rely on ORMs so they don't have to. The result is miserable performance and reimplementing parts of SQL badly in the application itself.
- frogamel 3y agoDplyr actually supports some really cool join functionalities that I wish were in SQL implementations, including: - Ability to specify whether your join should be one-to-one, many-to-one, etc. So that R will throw an error instead of quietly returning 100x as many rows as expected (which I've seen a lot in SQL pipelines). - A direct anti_join function. Much cleaner than using LEFT JOIN... WHERE b IS NULL to replicate an anti join. - Support for rolling joins. E.g. for each user, get the price of their last transaction. Super common but can be a pain in SQL since it requires nested subqueries or CTEs.
- sa-code 3y agoPolars does this too!