H. Combining Data

So you want to combine your table with another. Let's first take a look at joins to accomplish this goal. A JOIN clause is used to combine rows from two or more tables allowing you to fetch combined results.

For this example let's imagine we're joining with another table called 'booking_listings' which contains listings from the website Booking.com.

1. INNER JOIN

INNER JOIN selects records that have matching values in both tables.

1SELECT name
2FROM airbnb_listings
3INNER JOIN booking_listings
4ON airbnb_listings.host_id = booking_listings.host_id;

2. LEFT JOIN

LEFT JOIN selects records from the left table that match records in the right table. In the below example the left table is airbnb_listings.

1SELECT name
2FROM airbnb_listings
3LEFT JOIN booking_listings
4ON airbnb_listings.host_id = booking_listings.host_id;

3. RIGHT JOIN

RIGHT JOIN selects records from the right table that match records in the left table. In the below example the right table is booking_listings.

1SELECT name
2FROM airbnb_listings
3RIGHT JOIN booking_listings
4ON airbnb_listings.host_id = booking_listings.host_id;

4. FULL JOIN

FULL JOIN selects records that have a match in the left or right table. Think of it as the “OR” JOIN compared with the “AND” JOIN (INNER JOIN).

1SELECT name
2FROM airbnb_listings
3FULL OUTER JOIN booking_listings
4ON airbnb_listings.host_id = booking_listings.host_id;

5. UNION

Union on the other hand allows you to append rows to each other. Unlike joins which append matching columns, union can append unrelated rows provided they have the same number and name of columns.

1SELECT price, name FROM  airbnb_listings
2UNION 
3SELECT price, name FROM booking_listings;

You can think of union as a way of combining the results of two queries. You can use the UNION ALL syntax to return all the data, regardless of duplicates.

1SELECT price, name FROM  airbnb_listings
2UNION ALL
3SELECT price, name FROM booking_listings;