What are multiple tables? 

In a database, a table is used to store data in the form of rows and columns. Rows are aligned horizontally and identified by one or more values in a specific column. Columns are vertically aligned and identified by a unique column name. 

To retrieve data or manipulate data in tables, query languages like structured query language (SQL) are used. SQL uses operations like DELETE, SELECT, JOIN, UNION, DROP, CREATE, INSERT, and UPDATE. 

In the real world, the data may be stored in multiple tables. For that reason, if the individual tables are combined, it would be easier to access or manipulate the data in the tables simultaneously. That is, when the need for multiple tables arises, the JOIN command is used to access data from multi-tables. Furthermore, tables having the same columns can be combined easily. Later, the queries are run against the resulting new table.

Terminology

• Primary key: A primary key in a database table is a column that identifies each table row uniquely. Primary keys should contain unique values, and the primary key column can never have null values. For example, the employee ID column will be the primary key in an employee database since it defines all the other rows uniquely.

• Composite key: There can be only one primary key for a table, and it should have one or multiple fields. If the table has a primary key with multiple fields, the key is called a composite key.

• Foreign key: A foreign key is an attribute in a table referring to the primary key of another table. Foreign keys need not necessarily have unique values.

• Database tables: A database table is used to store data in the database. A database is made up of one or multiple tables. Each table in the database consists of rows and columns.

Creation of database containing multiple tables

Before creating a database table, it is important to understand a few SQL commands:

• CREATE: The CREATE command is used to create a table or a database in SQL.
• USE: The USE command is used to select an existing database in the SQL when there are multiple databases in the SQL schema.
• INSERT INTO: The INSERT INTO command is used to insert values/ records into the table.

Consider the following example to create multiple tables - Department and Employee in a database Company.

Creating department table

Step 1: Use the following query to create a database:

CREATE DATABASE department;

Step 2: To use the database, write the following query:

USE department;

Step 3: Now, create the database table.

Create Table department(

   EMPLOYEE_ID int,

   EMPLOYEE_ SALARY int,

   EMPLOYEE_NAME Varchar(20),

   DEPT_ID Varchar(255));

The above SQL query will create a table having four columns - EMPLOYEE_ID, EMPLOYEE_ SALARY, EMPLOYEE_NAME, and DEPT_ID.

Step 4: To insert records in the database table, use the following command.

INSERT INTO department VALUES

(‘12’, ’23000’, ’TIM’, ‘CSE’),

(‘15’, ’53000’, ’ALEX’, ‘IT’),

(‘20’, ’63000’, ‘ALICE’, ‘CSE’),

Step 5: To verify the records in the database, the table uses the SELECT command.

SELECT * FROM department;

This will give the following output:

Creating employee table

Step 1: Use the following query to create a database:

CREATE DATABASE employee;

Step 2: To use the database write the following query:

USE employee;

Step 3: Now create the database table.

CREATE TABLE employee(

  EMPLOYEE_ID int,

  EMPLOYEE_Email Varchar(255),

  EMPLOYEE_City Varchar(20) );

This will create a new table having three columns -EMPLOYEE_ID, EMPLOYEE_Email, and EMPLOYEE_City.

Step 4: To insert records in the employee table, use the following command.

INSERT INTO employee VALUES

(‘12’, ’TIM@gmail.com’, ‘New York’),

(‘15’, ‘ALEX@gmail.com’, ‘Toronto’’),

(‘20’, ‘ALICE@gmail.com’, ‘Florida’),

Step 5: To verify the records in the database table, use the SELECT command.

SELECT * FROM department;

This will give the following output

Finally, multiple tables have successfully been created in the database.

Queries involving multiple tables

Multiple tables can be queried by performing JOIN operation using the common column found in both the tables.

SELECT

The SELECT command is used to retrieve data from a database table. The SELECT command can be combined with the FROM clause to retrieve data from the tables and integrate the results from multiple tables. The syntax to use SELECT for multiple tables is as follows:

SELECT table_name1.column_name1, table_name2.column_name2 FROM table_name1, table_name2

WHERE table_name1.column_name1 = table_name2.column_name1

Example: Consider the employee and department tables mentioned above. To select the column EMPLOYEE_ID and EMPLOYEE_City from the tables, the following command can be used:

SELECT department.DEPT_ID, employee.EMPLOYEE_SALARY, employee.EMPLOYEE_City FROM department, employee 

WHERE department.EMPLOYEE_ID = employee.EMPLOYEE_ID;

This will return the output:

JOIN

The join operator is used to fetch or retrieve data from multiple tables in SQL. This operator can insert or remove rows in the table used by the SQL server for data processing before any other step consumes the data. The following types of join operations can be used for multiple tables:

• Cross join – This join operation is widely known as a Cartesian product. It adds all the possible combinations of input table rows to a single (virtual) table.
• Inner join – The inner join operator begins by creating a Cartesian product and filters the result by deleting the rows that do not fulfill the predicate supplied in the ON clause. 
• Outer join – Outer join operation can be of three types namely left outer join, right outer join and full outer join. Like inner join, all these operations first generate the cartesian product of the tables and then filter the results to identify rows that match each table. In this join operation, the rows from one table are preserved and added to the virtual table after applying the first filter. The null values are given for attributes with no matching values in the second table.

Cross join query in SQL

Cross join query generates a combination of each row of the first table with each row of the second table. The ON clause is not required in this query since no matching rows will be present in the other tables. The syntax of cross join query will be as follows:

SELECT ColumnName_1,

ColumnName_2,

ColumnName_N,

FROM [Table_1]

CROSS JOIN [Table_2]

Example: Consider the following two tables.

Table 1:

Table 2:

For cross joining tables A and B, the following query will be used:

SELECT *

FROM A

CROSS JOIN B

Here, an asterisk (*) is used to select all the columns from the table. So, the result of the Cartesian join will be:

Inner join query in SQL

The inner join query in SQL is used to fetch data from multiple tables. It selects the records with matching values in two or more tables and returns them separately. It does not return the rows that do not have a matching value. The syntax for join operation is as follows:

SELECT column_name(s)

FROM TABLE1

INNER JOIN TABLE2

ON Table1.Column_Name = Table2.Column_Name;

The keyword INNER JOIN in the above syntax can be replaced by only JOIN. Both keywords have the same meaning.

Example: Consider the following two tables.

Table orders:

Table items:

To retrieve Order_ID, Order quantity, Item_price, and Item_Desc for all the orders having an item, the query will be:

SELECT orders.Order_Id, Orders.qty, items.Item_price, items.Item_Desc

FROM orders

INNER JOIN items

ON orders.Item_ID = items.Item_ID

The above query will return the following output:

Here, Order_ID 4 was not included in the output since Item_ID 3 does not exist in the items table.

Outer join query in SQL

There are three types of SQL outer joins: left outer join, right outer join, and full outer join.

SQL full outer join

A full join is a combination of left and right join. If there are common rows between tables, it returns both left and right-hand side rows. Syntax of full outer join query is:

SELECT Column_Name(s)

FROM TABLE1

FULL OUTER JOIN TABLE2

ON Table1.Column_Name = table2.Column_Name

WHERE condition;

SQL left outer join

If the left-hand side of the table contains common rows, the left join operation can be used to return the common rows. It returns all rows from the left-hand side table even though the right part of the table has no common factor. Syntax of left outer join query is:

SELECT Column_Name(s)

FROM TABLE1

LEFT JOIN TABLE2

ON Table1.Column_Name = Table2.Column_Name;

SQL right outer join

If the right-hand side of the table contains common rows, the right join operation can be used to return the common rows. It returns all rows from the right-hand side of the table even if the left side of the table is no common factor. Syntax of left outer join query is:

SELECT Column_Name(s)

FROM TABLE1

RIGHT JOIN TABLE2

ON Table1.Column_Name = Table2.Column_Name;

Example: Consider the orders and items tables given in the inner join example above. To retrieve the Order_ID, Order quantity, Item_price, Item_desc, if a left join query is executed, it will not return the order details when there is no Item_ID in the items table. Instead, it will return NULL. So, by executing the following Left outer join query:

SELECT orders.Order_ID, orders.qty, items.Item_price, items.Item_Desc

FROM orders

LEFT JOIN items

ON orders.Item_Id = items.Item_Id

The result will be as follows:

Subqueries

A subquery is a query nested in another query and embedded in the WHERE statement. The WHERE statement defines a condition while retrieving data from one or multiple tables. A subquery can be used by combining it with SELECT, INSERT, UPDATE, or DELETE commands, and it should be enclosed in parentheses.

Following is the syntax of using a subquery with SELECT command:

SELECT column_name

FROM table_name

WHERE column_name expression operator 

    ( SELECT column_name  

    FROM table_name

    WHERE ... );

Consider two tables students and marks having the column STUDENT_ID in common:

Student table:

Marks table:

To find the STUDENT_ID and STUDNT_NAME, and STUDENT_MARKS of students who scored above 70, the following subquery can be used:

SELECT a.STUDENT_ID, a.STUDENT_NAME, b.STUDENT_MARKS

FROM Student a, Marks b

WHERE a.studentid = b.studentid

AND b.STUDENT_MARKS >70;

The output will be:

Context and Applications

SQL is a big part of data management and having a theoretical base on multiple tables is part of data manipulation. This theory is included in database management courses and computer courses like:

• Bachelors in Computer Applications
• Masters in Computer Applications
• Bachelors in Computer Science

Practice Problems

1. Which of the following is not a valid clause in SQL?? 

1. SELECT 
2. FROM 
3. BY 
4. WHERE 

Answer: Option c

Explanation: SELECT, JOIN, DELETE, FROM, UNION, DROP, CREATE, INSERT are types of SQL operations but BY is not an SQL operation.

2. What is the process used for reducing redundancy of a database? 

1. Normalization 
2. Many-to-many denormalization 
3. WHERE clause operation 
4. YYYY-MM-DD operation 

Answer: Option a

Explanation: The process of reducing redundancy in a given database is called normalization.

3. Which is the incorrect mode of JOIN operation? 

1. Outer
2. Cross
3. Inner 
4. Upward

Answer: Option d

Explanation: Upward JOIN is not a JOIN operation.

4. Which of the following is the correct full form for SQL? 

1. Structured question language 
2. Structured query language 
3. SELECT statement question language 
4. Server Query id column-language 

Answer: Option b

Explanation: The full form of SQL is- structured query language.

5. Which of the following is used to retrieve or manipulate data in multiple tables? 

1. Foreign key columns
2. Query languages like SQL
3. Primary key
4. DD-MM-YYYY FROM clause 

Answer: Option b

Explanation: Query languages like SQL are used to manipulate or retrieve information/ data from tables in a database.

Common Mistakes 

Structured Query language has its own rules that can affect the output. The correct syntax of SQL statements should be used for creating the expected table. Because of its wide application in a data table, multiple tables should be made with careful selection of the row and column and its associating attributes.

Related Concepts 

• SUBQUERIES 
• SQL Server 
• RDBMS 
• DBeaver 
• Tableau Software