Where To Register To Vote In Bedford Tx

Starting hither? This lesson is part of a total-length tutorial in using SQL for Information Analysis. Cheque out the beginning.

In this lesson we'll cover:

• Filtering in the ON clause
• Filtering in the WHERE clause
• Practice issues

Filtering in the ON clause

Usually, filtering is processed in the WHERE clause once the two tables take already been joined. Information technology's possible, though that you lot might desire to filter ane or both of the tables before joining them. For example, y'all just desire to create matches betwixt the tables under sure circumstances.

Using Crunchbase data, let'due south have another look at the LEFT Join example from an earlier lesson (this time we'll add an ORDER By clause):

          SELECT companies.permalink AS companies_permalink,        companies.name Every bit companies_name,        acquisitions.company_permalink Equally acquisitions_permalink,        acquisitions.acquired_at AS acquired_date   FROM tutorial.crunchbase_companies companies   LEFT JOIN tutorial.crunchbase_acquisitions acquisitions     ON companies.permalink = acquisitions.company_permalink  Gild BY one                  

Compare 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_permalink is '/company/1000memories':

          SELECT companies.permalink AS companies_permalink,        companies.name Equally companies_name,        acquisitions.company_permalink Every bit acquisitions_permalink,        acquisitions.acquired_at AS acquired_date   FROM tutorial.crunchbase_companies companies   LEFT JOIN tutorial.crunchbase_acquisitions acquisitions     ON companies.permalink = acquisitions.company_permalink    AND acquisitions.company_permalink != '/visitor/1000memories'  ORDER BY 1                  

What'southward happening higher up is that the provisional statement AND... is evaluated before the join occurs. You can call back of it as a WHERE clause that only applies to one of the tables. You can tell that this is simply happening in i of the tables because the 1000memories permalink is all the same displayed in the column that pulls from the other tabular array:

Filtering in the WHERE clause

If you move the same filter to the WHERE clause, you will discover that the filter happens after the tables are joined. The result is that the 1000memories row is joined onto the original table, simply and then information technology is filtered out entirely (in both tables) in the WHERE clause before displaying results.

          SELECT companies.permalink AS companies_permalink,        companies.name AS companies_name,        acquisitions.company_permalink AS acquisitions_permalink,        acquisitions.acquired_at AS acquired_date   FROM tutorial.crunchbase_companies companies   LEFT JOIN tutorial.crunchbase_acquisitions acquisitions     ON companies.permalink = acquisitions.company_permalink  WHERE acquisitions.company_permalink != '/visitor/1000memories'     OR acquisitions.company_permalink IS NULL  ORDER BY ane                  

You can encounter that the 1000memories line is not returned (it would have been betwixt the ii highlighted lines beneath). 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.

Sharpen your SQL skills

For this gear up of practice bug, we're going to introduce a new dataset: tutorial.crunchbase_investments. This table is also sourced from Crunchbase and contains much of the same information as the tutorial.crunchbase_companies data. Information technology it structured differently, though: it contains 1 row per investment. There can be multiple investments per company—it's even possible that one investor could invest in the same visitor multiple times. The column names are pretty cocky-explanatory. What'southward important is that company_permalink in the tutorial.crunchbase_investments table maps to permalink in the tutorial.crunchbase_companies tabular array. Go along in mind that some random data has been removed from this table for the sake of this lesson.

It is very likely that you will need to do some exploratory assay on this table to understand how y'all might solve the following problems.

Practise Problem

Write a query that shows a company'due south name, "status" (found in the Companies table), and the number of unique investors in that company. Lodge by the number of investors from nigh to fewest. Limit to merely companies in the state of New York.

Endeavor it out Meet the answer

Practice Problem

Write a query that lists investors based on the number of companies in which they are invested. Include a row for companies with no investor, and order from well-nigh companies to least.

Try information technology out Encounter the reply

Source: https://mode.com/sql-tutorial/sql-joins-where-vs-on/

Posted by: alcarazderming.blogspot.com

Share this post