<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Introduction
In the Intro to SQL micro-course, you learned how to use INNER JOIN to consolidate information from two different tables.  Now you'll learn about a few more types of JOIN, along with how to use UNIONs to pull information from multiple tables.  
Along the way, we'll work with two imaginary tables, called owners and pets. 
Each row of the owners table identifies a different pet owner, where the ID column is a unique identifier.  The Pet_ID column (in the owners table) contains the ID for the pet that belongs to the owner (this number matches the ID for the pet from the pets table).
For example, 
- the pets table shows that Dr. Harris Bonkers is the pet with ID 1.
- The owners table shows that Aubrey Little is the owner of the pet with ID 1.
Putting these two facts together, Dr. Harris Bonkers is owned by Aubrey Little.  Likewise, since Veronica Dunn does not have a corresponding Pet_ID, she does not have a pet.  And, since 5 does not appear in the Pet_ID column, Maisie does not have an owner.
JOINs
Recall that we can use an INNER JOIN to pull rows from both tables where the value in the Pet_ID column in the owners table has a match in the ID column of the pets table.
In this case, Veronica Dunn and Maisie are not included in the results.  But what if we instead want to create a table containing all pets, regardless of whether they have owners?  Or, what if we want to combine all of the rows in both tables?  In these cases, we need only use a different type of JOIN.
For instance, to create a table containing all rows from the owners table, we use a LEFT JOIN.  In this case, "left" refers to the table that appears before the JOIN in the query.  ("Right" refers to the table that is after the JOIN.)
Replacing INNER JOIN in the query above with LEFT JOIN returns all rows where the two tables have matching entries, along with all of the rows in the left table (whether there is a match or not).  
If we instead use a RIGHT JOIN, we get the matching rows, along with all rows in the right table (whether there is a match or not).
Finally, a FULL JOIN returns all rows from both tables.  Note that in general, any row that does not have a match in both tables will have NULL entries for the missing values.  You can see this in the image below.
UNIONs
As you've seen, JOINs horizontally combine results from different tables.  If you instead would like to vertically concatenate columns, you can do so with a UNION.  The example query below combines the Age columns from both tables.
Note that with a UNION, the data types of both columns must be the same, but the column names can be different.  (So, for instance, we cannot take the UNION of the Age column from the owners table and the Pet_Name column from the pets table.) 
We use UNION ALL to include duplicate values - you'll notice that 9 appears in both the owners table and the pets table, and shows up twice in the concatenated results.  If you'd like to drop duplicate values, you need only change UNION ALL in the query to UNION DISTINCT.
Example
We'll work with the Hacker News dataset. We begin by reviewing the first several rows of the comments table. (The corresponding code is hidden, but you can un-hide it by clicking on the "Code" button below.)
Step1:  You'll also work with the stories table.
Step3:  Since you are already familiar with JOINs from the Intro to SQL micro-course, we'll work with a relatively complex example of a JOIN that uses a common table expression (CTE).
The query below pulls information from the stories and comments tables to create a table showing all stories posted on January 1, 2012, along with the corresponding number of comments.  We use a LEFT JOIN so that the results include stories that didn't receive any comments.
Step4:  Since the results are ordered by the num_comments column, stories without comments appear at the end of the DataFrame.  (Remember that NaN stands for "not a number".)
Step6:  Next, we write a query to select all usernames corresponding to users who wrote stories or comments on January 1, 2014.  We use UNION DISTINCT (instead of UNION ALL) to ensure that each user appears in the table at most once.
Step7:  To get the number of users who posted on January 1, 2014, we need only take the length of the DataFrame.
<ASSISTANT_TASK:>
Python Code:
#$HIDE_INPUT$
from google.cloud import bigquery
# Create a "Client" object
client = bigquery.Client()
# Construct a reference to the "hacker_news" dataset
dataset_ref = client.dataset("hacker_news", project="bigquery-public-data")
# API request - fetch the dataset
dataset = client.get_dataset(dataset_ref)
# Construct a reference to the "comments" table
table_ref = dataset_ref.table("comments")
# API request - fetch the table
table = client.get_table(table_ref)
# Preview the first five lines of the table
client.list_rows(table, max_results=5).to_dataframe()
Explanation: Introduction
In the Intro to SQL micro-course, you learned how to use INNER JOIN to consolidate information from two different tables.  Now you'll learn about a few more types of JOIN, along with how to use UNIONs to pull information from multiple tables.  
Along the way, we'll work with two imaginary tables, called owners and pets. 
Each row of the owners table identifies a different pet owner, where the ID column is a unique identifier.  The Pet_ID column (in the owners table) contains the ID for the pet that belongs to the owner (this number matches the ID for the pet from the pets table).
For example, 
- the pets table shows that Dr. Harris Bonkers is the pet with ID 1.
- The owners table shows that Aubrey Little is the owner of the pet with ID 1.
Putting these two facts together, Dr. Harris Bonkers is owned by Aubrey Little.  Likewise, since Veronica Dunn does not have a corresponding Pet_ID, she does not have a pet.  And, since 5 does not appear in the Pet_ID column, Maisie does not have an owner.
JOINs
Recall that we can use an INNER JOIN to pull rows from both tables where the value in the Pet_ID column in the owners table has a match in the ID column of the pets table.
In this case, Veronica Dunn and Maisie are not included in the results.  But what if we instead want to create a table containing all pets, regardless of whether they have owners?  Or, what if we want to combine all of the rows in both tables?  In these cases, we need only use a different type of JOIN.
For instance, to create a table containing all rows from the owners table, we use a LEFT JOIN.  In this case, "left" refers to the table that appears before the JOIN in the query.  ("Right" refers to the table that is after the JOIN.)
Replacing INNER JOIN in the query above with LEFT JOIN returns all rows where the two tables have matching entries, along with all of the rows in the left table (whether there is a match or not).  
If we instead use a RIGHT JOIN, we get the matching rows, along with all rows in the right table (whether there is a match or not).
Finally, a FULL JOIN returns all rows from both tables.  Note that in general, any row that does not have a match in both tables will have NULL entries for the missing values.  You can see this in the image below.
UNIONs
As you've seen, JOINs horizontally combine results from different tables.  If you instead would like to vertically concatenate columns, you can do so with a UNION.  The example query below combines the Age columns from both tables.
Note that with a UNION, the data types of both columns must be the same, but the column names can be different.  (So, for instance, we cannot take the UNION of the Age column from the owners table and the Pet_Name column from the pets table.) 
We use UNION ALL to include duplicate values - you'll notice that 9 appears in both the owners table and the pets table, and shows up twice in the concatenated results.  If you'd like to drop duplicate values, you need only change UNION ALL in the query to UNION DISTINCT.
Example
We'll work with the Hacker News dataset. We begin by reviewing the first several rows of the comments table. (The corresponding code is hidden, but you can un-hide it by clicking on the "Code" button below.)
End of explanation
# Construct a reference to the "stories" table
table_ref = dataset_ref.table("stories")
# API request - fetch the table
table = client.get_table(table_ref)
# Preview the first five lines of the table
client.list_rows(table, max_results=5).to_dataframe()
Explanation: You'll also work with the stories table.
End of explanation
# Query to select all stories posted on January 1, 2012, with number of comments
join_query = 
             WITH c AS
             (
             SELECT parent, COUNT(*) as num_comments
             FROM `bigquery-public-data.hacker_news.comments` 
             GROUP BY parent
             )
             SELECT s.id as story_id, s.by, s.title, c.num_comments
             FROM `bigquery-public-data.hacker_news.stories` AS s
             LEFT JOIN c
             ON s.id = c.parent
             WHERE EXTRACT(DATE FROM s.time_ts) = '2012-01-01'
             ORDER BY c.num_comments DESC
             
# Run the query, and return a pandas DataFrame
join_result = client.query(join_query).result().to_dataframe()
join_result.head()
Explanation: Since you are already familiar with JOINs from the Intro to SQL micro-course, we'll work with a relatively complex example of a JOIN that uses a common table expression (CTE).
The query below pulls information from the stories and comments tables to create a table showing all stories posted on January 1, 2012, along with the corresponding number of comments.  We use a LEFT JOIN so that the results include stories that didn't receive any comments.
End of explanation
# None of these stories received any comments
join_result.tail()
Explanation: Since the results are ordered by the num_comments column, stories without comments appear at the end of the DataFrame.  (Remember that NaN stands for "not a number".)
End of explanation
# Query to select all users who posted stories or comments on January 1, 2014
union_query = 
              SELECT c.by
              FROM `bigquery-public-data.hacker_news.comments` AS c
              WHERE EXTRACT(DATE FROM c.time_ts) = '2014-01-01'
              UNION DISTINCT
              SELECT s.by
              FROM `bigquery-public-data.hacker_news.stories` AS s
              WHERE EXTRACT(DATE FROM s.time_ts) = '2014-01-01'
              
# Run the query, and return a pandas DataFrame
union_result = client.query(union_query).result().to_dataframe()
union_result.head()
Explanation: Next, we write a query to select all usernames corresponding to users who wrote stories or comments on January 1, 2014.  We use UNION DISTINCT (instead of UNION ALL) to ensure that each user appears in the table at most once.
End of explanation
# Number of users who posted stories or comments on January 1, 2014
len(union_result)
Explanation: To get the number of users who posted on January 1, 2014, we need only take the length of the DataFrame.
End of explanation
<END_TASK>