Menu
I have two existing tables, with different fields, except for Primary ID (a varchar, not an int). I want to create a third table which is essentially a merge of these two, such that for a given Primary Key I have all fields in one table.
How can this be done?
464
Answer: To do this, the SQL CREATE TABLE syntax is: CREATE TABLE new_table AS (SELECT * FROM old_table WHERE 1=2); For example: CREATE TABLE suppliers AS (SELECT * FROM companies WHERE 1=2);
A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. The new table has the same column definitions. All columns or specific columns can be selected.
A JOIN does not create a new table, or even a virtual table. Rather it simply describes a relational algebra operation. The SQL implementation only needs to use this operation to generate a compatible result set.
Ans: Joining two tables in SQL can be done in four major ways: Inner Join (returns rows with matching columns), Left Join (ALL records in the left table and matching records in the right table), Right Join (ALL records in the right table and matching records in the left table), and Union (removes duplicates).
SQL JOIN. A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Relational algebra is the most common way of writing a query and also the most natural way to do so. The code is clean, easy to troubleshoot, and unsurprisingly, it is also the most efficient way to join two tables.
CREATE TABLE result AS
(SELECT first.*,
second.f1,
second.f2,
second.f3
FROM first
INNER JOIN second
ON first.id = second.id);
To get a view, do the same except replace "TABLE" with "VIEW". If you go with the table rather than the view, make sure to add a primary key as that will not be added by default.
169
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
