SQL Multiple Tables

Question 1

why might we want to use multiple tables in a database schema?

Answer 1

if you store a lot of data in columns and have many entries (rows) much of this data will be repeated which will make your table hard to work with. this is called “avoiding data redundancy.”

Question 2

what is joining two tables?

Answer 2

will combine rows from different tables if the join condition is true.

Question 3

what is the purpose of a JOIN sequence?

Answer 3

to combine 2 tables

Question 4

When doing an INNER JOIN, are columns matched on NULL values?

Answer 4

No, when you have NULL values in a column, these are never matched to other NULL values. This is because NULL signifies the absence of any value, and cannot be compared as they will never equal anything. Doing say NULL = NULL results in False.

Question 5

What happens if you try to perform a join but the rows don’t match?

Answer 5

When we perform a simple JOIN (often called an inner join) our result only includes rows that match our ON condition.

Question 6

When we write a JOIN query, does the order we specify the tables matter?

Answer 6

Generally, no, the order of the tables in the JOIN will not affect the overall results of the query.

As long as you specify what columns to select, the results should appear essentially the same, just that the rows will be ordered according to the appearance in the first table.

However, if you do not specify specific columns, then the order of columns will be different depending on the order of the tables. Without specifying columns in your SELECT, by using just SELECT *, the result query will order the columns by the first table’s columns followed by the second table’s columns.

Question 7

what is a left join?

Answer 7

ill return every row in the left table, and if the join condition is not met, NULL values are used to fill in the columns from the right table.

Question 8

what is the syntax of the left join?

Answer 8

select *
from table1
left join table2
on table1.c2=table2.c2

where the last line specifie which columns to match the table on.

Question 9

when should we use inner join vs. left join?

Answer 9

Generally, we use INNER JOIN when we want to select only rows that match an ON condition. If no rows match the ON condition, then it will not return any results. This can be somewhat stricter than using a LEFT JOIN.

We use a LEFT JOIN when we want every row from the first table, regardless of whether there is a matching row from the second table. This is similar to saying,
“Return all the data from the first table no matter what. If there are any matches with the second table, provide that information as well, but if not, just fill the missing data with NULL values.”

In a way, LEFT JOIN is less strict than INNER JOIN. Furthermore, the results of a LEFT JOIN will actually include all results that an INNER JOIN would have provided for the same given condition.

Question 10

what are the requirements for a primary key?

Answer 10

None of the values can be NULL.
Each value must be unique (i.e., you can’t have two customers with the same customer_id in the customers table).
A table can not have more than one primary key column.

Question 11

define foreign key

Answer 11

When the primary key for one table appears in a different table

Question 12

what is a common type of join to be familiar with?

Answer 12

The most common types of joins will be joining a foreign key from one table with the primary key from another table.

Question 13

what is a cross join?

Answer 13

lets us combine all rows of one table with all rows of another table.

Question 14

can you cross join more than 2 tables?

Answer 14

Yes, you can CROSS JOIN as many tables as you want.

Question 15

what are the 2 rules for a union (appending data to other data)?

Answer 15

1 Tables must have the same number of columns.
2 The columns must have the same data types in the same order as the first table.

Question 16

What happens if tables we perform UNION on have duplicate rows?

Answer 16

duplicate rows will be excluded.

Question 17

what if you want to union tables and include duplicates?

Answer 17

some versions of sql have the UNION ALL operator.

Question 18

what does a WITH statement do?

Answer 18

allows us to perform a separate query (such as aggregating customer’s subscriptions)

Essentially, we are putting a whole first query inside the parentheses () and giving it a name. After that, we can use this name as if it’s a table and write a new query using the first query.

Question 19

what does Previous_results mean?

Answer 19

previous_results is the alias that we will use to reference any columns from the query inside of the WITH clause

Question 20

what is a union?

Answer 20

UNION stacks one dataset on top of another.

Question 21

what is a with statement?

Answer 21

WITH allows us to define one or more temporary tables that can be used in the final query.

Question 22

when do we use a join/inner join statement?

Answer 22

Use JOIN, or INNER JOIN, when you want to strictly select rows of data that match some condition, provided by an ON clause. For example, if we had tables of employee information, and require their company information, we could JOIN the tables together so that we only obtain results that match, and provide us all the information we need, excluding results that are missing information.

Question 23

when do we use a left join statement?

Answer 23

LEFT JOIN can be used when you want to see all the results from the first table no matter what, but also want to include matches, if any, with the second table. For example, say we had a table for customers and another table for purchase information. If we wanted to obtain all customer information, and any purchase information, then a LEFT JOIN might be useful. If the customers made no purchases, their information will still be returned.

so all of left table and matches, if any, with right table. if no data in right table corresponding to left table’s records, left table’s info will still be returned.

Question 24

when do we use a cross join?

Answer 24

CROSS JOIN can be used when you want to get all possible combinations of rows from a table with other tables. A simplified example of using this would be, say we wanted to get every possible combination of a meal, which consists of an appetizer, main course, and dessert. Using a CROSS JOIN can give us every possible combination.

Question 25

when do we use a union?

Answer 25

Although not necessarily a JOIN, UNION can be used when you wish to combine multiple tables together quickly. One helpful feature of a UNION is that it will only return unique rows, so there will be no duplicates in the combined table.

Question 26

what is the syntax of a cross join?

Answer 26

SELECT * FROM [Table1] CROSS JOIN [Table2]

NOTE: There is no need for a join condition as all rows are returned.

Question 27

what is the union syntax?

Answer 27

SELECT *
FROM table1
UNION
SELECT *
FROM table2

Question 28

what is the difference between join/inner join and left join?

Answer 28

left join combines rows from two or more tables, but unlike inner join, does not require the join condition to bet met.