How to 'inner join' two tables?

Hi guys,

I wonder does anyone know how to join two tables (say two csv files) like using inner join on SQL on snap? Thanks!

Is this for an assignment?

He said that he's wondering, so it might not be, although it does sound like an assignment.

No it’s not. I’m actually the TA of a course we are trying to develop. We wanna teach students how to use the SQL blocks in Snap so I’m curious about how to combine two tables

Are you talking about the SciSnap! SQL Block? Because there appear to be no blocks for explicit SQL joins, an implicit one should be used:
SELECT * FROM A,B
WHERE
filed_of_a = field_of_b

Or maybe just multicolumn list operation. Ad'hoc implementation, matching first columns

Hi @yangfuchun and @guzdial,

great question! Sorry it didn't yet get answered in more detail. I was on vacation and then at a conference and only saw it today. The idea is that in Snap! we treat tables as 2D lists (of lists), i.e. a table is a list of rows or records. Then we use HOFs (higher order functions) to filter ("keep"), transform ("map") and reduce ("combine") data.

Looking at this example:

let's take the same 2 tables, "foods" and "companies":

We can now formulate our own HOF that performs the SQL-equivalent of an "inner join". It would be used like this:

Note how the expression inside the grey ring is a predicate that expects 2 inputs, one representing each record in the left table, and the other for each record in the right table. Also note that the predicate expression is free, you don't have to use equality, instead you can link both tables in alternative - even wacky - ways, .e.g. like this:

Here's my - somewhat naive - way in which I would build this "inner join" function myself in Snap!:

Similarly you could make your own custom blocks that mimick other SQL operations. I'm not sure whether students would actually create such complex HOF blocks themselves, but you could develop them yourself and just give them to your students so they can explore the data concepts with them.

Does that help?

Oh, I forgot: If you would like to play with this example project, here goes: Snap! Build Your Own Blocks

Hi Jens,

Thank you so much for the reply! It will definitely help for us to figure out how to teach students the inner join on Snap! :slight_smile: