Keep in mind that some random data has been removed from this table for the sake of this lesson. What's important is that company_ in the tutorial.crunchbase_investments table maps to in the tutorial.crunchbase_companies table. The column names are pretty self-explanatory. There can be multiple investments per company-it's even possible that one investor could invest in the same company multiple times. It it structured differently, though: it contains one row per investment. This table is also sourced from Crunchbase and contains much of the same information as the tutorial.crunchbase_companies data.
Sharpen your SQL skillsįor this set of practice problems, we're going to introduce a new dataset: tutorial.crunchbase_investments. Also note that filtering in the WHERE clause can also filter null values, so we added an extra line to make sure to include the nulls. You can see that the 1000memories line is not returned (it would have been between the two highlighted lines below). The result is that the 1000memories row is joined onto the original table, but then it is filtered out entirely (in both tables) in the WHERE clause before displaying results. There are two different types of syntax for inner joins. If you move the same filter to the WHERE clause, you will notice that the filter happens after the tables are joined. A join that identifies combinations of matching rows from two tables is called as an inner join. You can tell that this is only happening in one of the tables because the 1000memories is still displayed in the column that pulls from the other table: Filtering in the WHERE clause You can think of it as a WHERE clause that only applies to one of the tables. What's happening above is that the conditional statement AND. AS companies_,ĪND pany_ != '/company/1000memories' LEFT JOIN tutorial.crunchbase_acquisitions acquisitionsĬompare the following query to the previous one and you will see that everything in the tutorial.crunchbase_acquisitions table was joined on except for the row for which company_ is '/company/1000memories': SELECT companies. AS companies_,Īcquisitions.acquired_at AS acquired_dateįROM tutorial.crunchbase_companies companies Using Crunchbase data, let's take another look at the LEFT JOIN example from an earlier lesson (this time we'll add an ORDER BY clause): SELECT companies. For example, you only want to create matches between the tables under certain circumstances.
It's possible, though that you might want to filter one or both of the tables before joining them. Normally, filtering is processed in the WHERE clause once the two tables have already been joined. Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis.
0 kommentar(er)
