The same number of columns for the Union operation
I was going to combine and combine all the logic and try examples. What puzzled me is why it is necessary to have the same number of columns in both tables to perform the join or the whole operation?
Forgive me if my question is stupid, but I couldn't get an exact answer anywhere. And conceptual thinking, if one common column is present, the merge of the two tables should be easily correct (like a join operation). But it is not, and I want to know why?
source to share
JOIN operations do NOT require the same number of columns to be selected in both tables. UNION operations are different from each other. Think of it as two separate lists of data that can be "pasted" together into one large block. You cannot have columns that don't match.
Another way to look at this, JOIN does this:
TableA.Col1, TableA.Col2 .... TableB.Col1, TableB.Col2
UNION does the following:
TableA.Col1, TableA.Col2
TableB.Col1, TableB.Col2
JOINS add columns to rows, UNIONS adds more rows to existing rows. This is why they must "match."
Matt
source to share
Join the operation combines columns from two tables
.
Where in Union and Union is everything combines two results sets
, so to combine two results you need to have the same number of columns with compatible data types.
In the real world, for example. To play cricket you need 11 players on both teams, 7 on one team and 11 on the opposite team are not allowed.
source to share
Suppose you have an EMPTable with columns and values as shown below: id, name, address, salary, DOB 1, 'SAM', '2 Merck Ln', 100000, '08 / 18/1980 '
IF you only want UNION to have a column name (let's say the value is TOBY), that means you have to use other defaults for NULL (clever software or db can implicitly do this for you), which essentially translates below (to prevent the integrity of the relational table) →
1, 'SAM', '2 Merck Ln', 100000, '08/18/1980'
UNION
NULL,'TOBY', NULL, NULL, NULL
A "union" is by definition a merge of (different) values of the same class or type.
source to share
You need to investigate the difference between UNION
and JOIN
.
You mainly use UNION
when you want to get records from one source (table, view, group of tables, etc.) and combine those results with records from another source. They must have the same columns for a common result set.
You use JOIN
when you want to join records from one source with another source. There are several types of JOINs (INNER, LEFT, RIGHT, etc.) depending on your needs. When using JOINs, you can specify which columns you need.
Good luck.
source to share
join is mainly used when we want to retrieve data from two or more tables and there is no need to retrieve the same number of columns for that, consider the situation where we split the table into two parts using normalization concepts,
emp [eid, ename, edob, esal] emp_info [eid, emob_no]
here I want to know the name and mobile no. of all employees, then we will use the concept of union, because here the information we need cannot be provided by one table.
so we will use ..
SELECT E.ENAME, EI.EMOB_NO FROM EMP E, EMP_INFO EI WHERE E.EID = EI.EID AND LOWER (E.ENAME) = 'john';
now consider a situation where we want to find employees who have a savings account and a bank loan account. Here we want to find common tuples from two table results. for this we will use set operations. [intersection]
for the specified operations 2 conditions MUST BE met.
-
same. attributes should be a selection from each table.
-
the domain of each attribute must be the same compatible with the higher ...
source to share