Class notes
CIS 2200 Joining Data in SQL Notes
- Course
- CIS 2200
- Institution
- Baruch College
This is a comprehensive and detailed note Joining Data in SQL for CIS 2200. *Essential Study Material!!
[Show more]
Content preview
Joining Data in SQL We use joins to combine multiple tables within a query.
The ON statement tells the SQL engine what columns to use to join the tables.
Joins come after the FROM clause.
• Inner Join is the most common way to join data using SQL. An inner join includes only rows that have a match as specified
by the ON clause.
SELECT table1.column1, table1.column2, table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
• Left Join includes all rows from an inner join, plus any rows from the first table that don't have a match in the second table.
SELECT table1.column1, table1.column2, table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
• Right Join includes all rows from the second table that don't have a match in the first table + any rows from an inner join.
SELECT table1.column1, table1.column2, table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
• Full Outer Join includes all rows from both joined tables.
SELECT table1.column1, table1.column2, table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
SQLite doesn't support full outer joins or right joins.
Joins with Subqueries
, Subqueries can be used to substitute parts of queries, allowing us to find the answers to more complex questions.
We can also join to the result of a subquery, just like we could a table.
The result of a subquery is calculated first, so we read
from the inside out.
This simple query selects all columns from cities, filtering rows that are marked as capital cities by having a value
for capital of 1.
The INNER JOIN joins the subquery result, aliased as c, to the facts table based on the ON clause.
Two columns are selected from the results of the join:
o f.name, aliased as country
o c.name, aliased as capital_city
The results are limited to the first 10 rows.
Joining 3 Tables
• A schema diagram helps us understand the available columns and the
structure of the data. In a schema diagram, relationships are shown using
lines between tables.
• Each row's primary key must be unique.
Exercise: Write a query that gathers data about the invoice with an invoice_id of 4. Include the following columns in
order:
o The id of the track, track_id
o The name of the track, track_name
o The name of the media type of the
track, track_type
o The price that the customer paid for the
track, unit_price
o The quantity of the track that was
purchased, quantity
Joining 3+ Tables
The benefits of buying summaries with Stuvia:
Guaranteed quality through customer reviews
Stuvia customers have reviewed more than 700,000 summaries. This how you know that you are buying the best documents.
Quick and easy check-out
You can quickly pay through credit card or Stuvia-credit for the summaries. There is no membership needed.
Focus on what matters
Your fellow students write the study notes themselves, which is why the documents are always reliable and up-to-date. This ensures you quickly get to the core!
Frequently asked questions
What do I get when I buy this document?
You get a PDF, available immediately after your purchase. The purchased document is accessible anytime, anywhere and indefinitely through your profile.
Satisfaction guarantee: how does it work?
Our satisfaction guarantee ensures that you always find a study document that suits you well. You fill out a form, and our customer service team takes care of the rest.
Who am I buying these notes from?
Stuvia is a marketplace, so you are not buying this document from us, but from seller anyiamgeorge19. Stuvia facilitates payment to the seller.
Will I be stuck with a subscription?
No, you only buy these notes for $11.99. You're not tied to anything after your purchase.
Can Stuvia be trusted?
4.6 stars on Google & Trustpilot (+1000 reviews)
75632 documents were sold in the last 30 days
Founded in 2010, the go-to place to buy study notes for 14 years now