About the condition with the same amount of columns in the tables: what if we only choose 2 columns per table to union but the original tables don’t have the same amount of columns, will it work?
let’s say the table newspaper has 5 columns and the table subscription has 3. if i only pick 2 per table for the union is that possible?
like:
select id, first_name
from newspaper
union
select id, start_month
from subscription;
Can someone please clarify for me the following: Are all of the queries in this exercise just that “queries” or do some actually change the tables, columns or data? Such as cross join or union, etc…
This might be a stupid question but why is it that when we JOIN two tables it is sufficient to simply SELECT * to display the resultant joined table, like so:
SELECT *
FROM table1
INNER JOIN table 2
However for a union, the method changes such that we have to specify SELECT * twice, like this:
SELECT *
FROM table1
UNION
SELECT *
FROM table2
If SELECT can be used to display the resultant joined table, why can it be used similarly like so:
What is the difference between Union and Cross Join? They both look the same to me but they were introduced as different things so I know they are not supposed to be the same.
How would we create a new column with all the same values before we UNION the two data sets?
In this example, we might want to know whether the subscription was online or newspaper, so how would we add a new column called subscription_type to newspaper and label every row as “newspaper” in that column; and in the online table, add a column where every row has an “online” label?
That way, when you UNION both data sets, we know which data came from where in case you need to reference it later. It seems like we should use this statement:
ALTER TABLE table_name
ADD COLUMN subscription_type text;
And then can you UPDATE the table to have all values of ‘Online’ for subscription_type? I feel like I have done this before and I can’t quite remember how to do it.
I have the same question. Is this simply a syntactic quirk of SQL? Or is there some particular reason the JOIN syntax would cause problems? For instance, it’s not quite clear to me what the difference is between a UNION and a CROSS JOIN. Although given that this question was asked 3 years ago I wonder if there will be an answer…
Queries such as Select generate a result that is held in memory. The source data tables are not affected. Certain SQL commands, such as Insert, Update, Delete do change the source data table.
) below!
) to up-vote the contribution!