The type of operation that retrieves data from two or more tables is called a

With SQL, you can get information from columns in more than one table. This operation is called a join operation.

In SQL, a join operation is specified by placing the names of those tables that you want to join in the same FROM clause of a SELECT statement.

Suppose that you want to see a list of all the suppliers and the item numbers and item names for their supplied items. The item name is not in the SUPPLIERS table; it is in the INVENTORY_LIST table. Using the common column, ITEM_NUMBER, you can see all of the columns as if they were from a single table.

Whenever the same column name exists in two or more tables being joined, the column name must be qualified by the table name to specify which column is being referenced. In this SELECT statement, the column name ITEM_NUMBER is defined in both tables, so it needs to be qualified by the table name. If the columns have different names, no qualification is needed.

To perform this join operation, enter the following SELECT statement by typing it directly on the Enter SQL Statements display or by prompting:

SELECT SUPPLIER_NUMBER, SAMPLECOLL.INVENTORY_LIST.ITEM_NUMBER, ITEM_NAME
      FROM SAMPLECOLL.SUPPLIERS, SAMPLECOLL.INVENTORY_LIST
      WHERE SAMPLECOLL.SUPPLIERS.ITEM_NUMBER
                         = SAMPLECOLL.INVENTORY_LIST.ITEM_NUMBER

Another way to enter the same statement is to use a correlation name. A correlation name provides another name for a table name to use in a statement. A correlation name must be used when the table names are the same. It can be specified by following each table name in the FROM list. The previous statement can be rewritten as:

SELECT SUPPLIER_NUMBER, Y.ITEM_NUMBER, ITEM_NAME
      FROM SAMPLECOLL.SUPPLIERS X, SAMPLECOLL.INVENTORY_LIST Y
      WHERE X.ITEM_NUMBER = Y.ITEM_NUMBER

In this example, SAMPLECOLL.SUPPLIERS is given a correlation name of X and SAMPLECOLL.INVENTORY_LIST is given a correlation name of Y. The names X and Y are then used to qualify the ITEM_NUMBER column name.

Running this example returns the following output.

                                 Display Data
                                             Data width . . . . . . :      45
Position to line  . . . . .              Shift to column  . . . . . .
....+....1....+....2....+....3....+....4....+
SUPPLIER_NUMBER  ITEM    ITEM
                 NUMBER  NAME
     1234        153047  Pencils, red
     1234        229740  Lined tablets
     1234        303476  Paper clips
     9988        153047  Pencils, red
     9988        559343  Envelopes, legal
     2424        153047  Pencils, red
     2424        303476  Paper clips
     5546        775298  Chairs, secretary
     3366        303476  Paper clips
     3366        073956  Pens, black
********  End of data  ********
 
F3=Exit      F12=Cancel      F19=Left      F20=Right      F21=Split

Note: Because no ORDER BY clause was specified for the query, the order of the rows returned by your query may be different.

The data values in the result table represent a composite of the data values contained in the two tables INVENTORY_LIST and SUPPLIERS. This result table contains the supplier number from the SUPPLIER table and the item number and item name from the INVENTORY_LIST table. Any item numbers that do not appear in the SUPPLIER table are not shown in this result table. The results are not guaranteed to be in any order unless the ORDER BY clause is specified for the SELECT statement. Because you did not change any column headings for the SUPPLIER table, the SUPPLIER_NUMBER column name is used as the column heading.

The following example shows how to use ORDER BY to guarantee the order of the rows. The statement first sorts the result table by the SUPPLIER_NUMBER column. Rows with the same value for SUPPLIER_NUMBER are sorted by their ITEM_NUMBER.

SELECT SUPPLIER_NUMBER,Y.ITEM_NUMBER,ITEM_NAME
	FROM SAMPLECOLL.SUPPLIERS X,SAMPLECOLL.INVENTORY_LIST Y
	WHERE X.ITEM_NUMBER = Y.ITEM_NUMBER
	ORDER BY SUPPLIER_NUMBER,Y.ITEM_NUMBER

If you're working with databases, at some point in your work you will likely need to use SQL JOINs. This guide offers a quick overview of SQL JOINs and introduces you to some of the types of JOINs used most commonly.

SQL JOIN definition and uses 

Let's start with an overview of what a database is. A database is a collection of different tables storing different types of information. The JOIN clause is used when retrieving data from related tables in a database. The SQL JOIN clause is more complex than a simple query that retrieves data from a single table because it retrieves data from multiple tables. 

Types of SQL JOINs with examples

You can choose among four types of SQL JOINs depending upon the results you desire; Inner JOIN, Left Outer JOIN, Right Outer JOIN, and Full Outer JOIN. Take a look at how each works, along with some sample SQL JOIN clauses:

Inner 

Inner JOINs combine two tables based on a shared key. For example, if you had a table with a column called "user id" and each user id was unique to a user, you could join that table to another table with a "user id" column to find the information associated with each user. This  example shows how to use an Inner JOIN clause to join two tables:

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

Left Outer

Left JOINs return all rows from the first table and only the rows in the second table that match. This example shows how to use a Left Outer JOIN clause to join two tables:

SELECT * FROM table1 LEFT OUTER JOIN table2  ON table1.id = table2.user_id

Right Outer

Right JOINs are logically the opposite of Left JOINs—they return all rows from the second table, and only the rows in the first table that match. This example shows how to use a Right Outer JOIN clause to join two tables:

SELECT * FROM table1  RIGHT OUTER JOIN table2 ON table1.id = table2.user_id

Full Outer

Full JOINs combine both left and right joins by returning all rows from both tables, as long as there is at least one match between them. This example shows how to use a Full Outer JOIN clause to join two tables:

SELECT * FROM table1  FULL OUTER JOIN table2 ON table1.id = table2.user_id

There are many cases for using SQL JOINs, and they are crucial when mapping out relationships between tables in your database. 

Introduction to Structured Query Language (SQL)

Imagine a table that stores personal information (name, address, phone number) and another table stores information related to employee job positions. Suppose each row on the employee table represents a single employee. In that case, it makes sense to store the employees' personal data in another table since an individual may be represented more than once (one row per position as they change roles).

Let's say that you need to write an application that shows employee names and addresses along with their current position, previous positions, and hire date. To retrieve this data from the database, you need to join these two tables together using some attributes common between them (such as Employee ID).

An e-commerce example of using SQL JOIN

Imagine now that you have an online store and want to know which products were bought by your customers. You would have two tables: one containing information about your customers and another containing information about your products. You can use an Inner JOIN to retrieve all the records that appear in both these tables using the following syntax:

Select * from customers Inner JOIN orders on customers.id = orders.customer_id;

Example with code

Consider a situation where you have two database tables, one called “Students” and the other called “Grades.” The “Students” table contains one record for each student: their ID number, name, major, and so on. The “Grades” table contains one record for each student's grade on different courses: their student ID number, the course they took, and their grade in the course.

In SQL, you would write a query to find the names of all students who have received a grade of 100 as follows:

• SELECT Students.StudentName FROM Students.

• JOIN Grades ON Students.StudentID=Grades.StudentID.

• WHERE Grades.Grade=100.

Combining JOINs 

There are many ways to combine results from two or more queries. Here are the most common:

• Use a JOIN statement to combine data from multiple tables in one SELECT statement.

• Use a subquery to retrieve data from one table based on values from another table.

• Use a UNION statement to combine multiple tables (or queries) data.

• A JOIN statement can be used with any other type of statement that SQL supports, including UPDATE and DELETE.

Tips for learning more about SQL JOINs

If you're looking to do SQL projects or to get a job using SQL, you may need to build your knowledge and skills. Make sure you learn from reliable materials. Check that the trainer or instructor has advanced competencies in SQL. Read reviews and analyze the coursework or learning structure.

Tutorials 

Many tutorials are available on the internet that can help you learn SQL. These tutorials are often free and provided by competent people in their field. Learning through tutorials requires some planning. If you choose this route, make sure you follow a logical learning structure to learn all the foundational building blocks for working with SQL. For example, you will need a solid understanding of databases.

Online courses

There are many online courses with which you can learn SQL. Some of these courses are free, and some charge a fee. Some of the paid courses are comprehensive and offer value for money. Courses provide you with a structured learning process and can be an excellent way to build knowledge. 

Certifications 

There are plenty of SQL certifications for you to choose from. Certificates allow you to demonstrate to employers that you have passed an examination testing your SQL knowledge and can be particularly helpful if your resume doesn't contain much SQL experience.

Next steps 

If you want to learn more about SQL, consider taking one of the courses on Coursera. The Introduction to Structured Query Language (SQL) course offered by the University of Michigan is a good place to start your journey. By taking this course, you can learn how to create a MySQL database step by step and learn more about the SQL language.

What is the function that is used to retrieve data FROM a database is called?

Which statement is used to retrieve data FROM more than one table?

What retrieves specific data FROM one or more tables to answer a question?

Which operation will allow you to extract data FROM more than one table?