SQL Table Join

To remove records from a table we use the DELETE statement.

The DELETE statement deletes rows from a table, alias, or view,
or the underlying tables, aliases, or views from the specified fullselect.

Deleting a row from an alias deletes the row from the data source object
that the alias refers to.

Deleting a row from a view deletes the row from the table on which the
view is based if no INSTEAD OF trigger has been defined for the delete
operation on this view.

If such a trigger has been defined, the trigger will be executed instead.

There are two forms of this statement:

• The DELETE with search form is used to delete one or more
  rows (optionally determined by a search condition).
• The DELETE form with position is used to delete exactly one
  row (determined by the current position of the cursor).

DELETE from student;

This statement displays a message indicating the number of records it has
deleted.

If you do not want to delete all the records, but only some, you must
indicate which one or which ones, for this, use the DELETE command
together with the WHERE clause with which you can establish the
condition that the records to be deleted must meet.

For example, you may want to delete that record whose name is “George”:

DELETE FROM student WHERE name=’George’;

If you request the deletion of a record that does not exist, that is, no
record meets the specified condition, no record will be deleted.

Note that if you don’t put a condition, all the records of the named
table are deleted.

For the example, you must create a table with the name ‘student’

CREATE TABLE `student` (`idStudent` int(11) DEFAULT NULL, `name`
varchar(50) DEFAULT NULL, `idSupervisor` int(11) DEFAULT NULL)
ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

And then, fill it with the following data:

INSERT INTO dbo.student (`idStudent`, `name`, `idSupervisor`)
VALUES
(1, ‘ANN’, 4),
(2, ‘GEORGE’, 4),
(3, ‘BEA’, 2),

(4, ‘CHRISTOPHER’, 3);

Now, if you want to delete the record that contains the name ‘GEORGE’,
you must write the following statement:

DELETE FROM dbo.student WHERE name=’George’;

Consider that if you do not delimit the statement with WHERE, you
are going to delete all the records of the table.

In general terms, a DELETE statement can be embedded in an
application program or issued using dynamic SQL statements.

This is an executable statement that can be dynamically prepared.

Lastly, this delete statement completes a trio with INSERT and UPDATE
for full control over the information stored in our databases.

Imagine that you have a more or less complex query that allows you to
obtain cross information from several tables with complicated conditions,
but what you really want is not exactly that data, but to be able to use
it later to make more filters with it in the same query.

For example, you pull a bunch of data from several related tables, with
combined or calculated fields, some extracted from XML fields, and then
you want to find out only which records have a certain different field, or
you want to make additional conditions based on the calculated fields.

Something similar to this:

SELECT Field1, Field2 FROM (SELECT complex)WHERE Conditions ORDER
BY Fields

This would be, in essence, a compound query, mathematically equivalent to
F(fx) or a function of a function.

It seems like the most natural thing in the world to be able to do it.

The problem is that SQL Server doesn’t let you do it most of the time.
Most likely, when trying to do so, you will get the following error
message:

Msg 102, Level 15, State 1, Line x Incorrect syntax near ‘)’.

Which is, of course, not a great clue as to what’s going on.

Also, if we are using it from the SQL Server Management Studio, it
underlines the name of the fields of the outer query as “invalid field
name”.

Common Table Expressions

Now, SQL Server offers us a quite interesting functionality called Common
Table Expressions or, according to its initials, CTE.

It is a way of defining a temporary data set (something like a temporary
table) that only lasts while our query is executed and is not stored
anywhere, that is, it does not take up space.

However, it allows us to consult it and work with it as if it were a real
database table.

These CTEs can refer to themselves, and can be used multiple times in the
same query.

One of the benefits of the CTE is that it opens the possibility of
creating recursive queries, a very interesting concept that gives us a lot
of power.

In addition to being used in recursive queries, they can be used, among
other things, to:

1. Filter in a simple way by fields that did not exist before.
2. Replace the use of views, which are often not necessary.
3. Referencing the same table multiple times in the same expression.
4. Group or filter by fields that are derived from subqueries.
5. Group or filter by fields that result from functions that are not
   deterministic (that is, they return a different value each time they are
   called, even passing the same arguments).

The basic syntax of a CTE is as follows:

WITH CTEName [(return fields)] AS (Subquery)SELECT * FROM CTEName

That is, we specify a name for the “temporary table” (CTE), an alias with
‘AS’, and then in parentheses the complex query we want to use as the
basis for the CTE.

Right after that we can enter the query to be carried out using the name
that we have given to the CTE as if it were another table.

If you want to change the default names, you can indicate in parentheses
the name of the fields that the subquery will return in order.

Although this is not used often, since we usually leave the default names
that they have or have been assigned.

Guidelines for creating and using common table expressions

The following guidelines apply to non-recursive common table expressions:

1. Note that recursive common table expressions allow us to reference
   themselves and can be used multiple times in the same query.
2. A CTE can be used in only one statement at a time (Select, Update,
   Insert, or Delete) that references some or all of the columns defined
   within the CTE.
3. A CTE can be specified within a view.
4. Specifying more than one WITH clause in a CTE is not allowed.
5. The following clauses cannot be used in a CTE: ORDER BY (except when
   the TOP clause is specified) , INTO, FOR BROWSE.
6. A query that references a CTE can be used to define a cursor.

Example

We want to obtain the list of the first 10 products that have the largest
quantity per Outer in the Warehouse.

As you can see we are defining a common table expression called
Quantity_outer , this CTE allows to return the quantity per outer for each
product.

With Quantity_outer As (Select Warehouse.StockItems.StockItemID,
SUM(QuantityPerOuter) QuantityPerOuter FROM [WareHouse].[StockItems]
GROUP by StockItemID) Select Top 10 A.StockItemID, A.StockItemName,
IsNull(B.QuantityPerOuter,0) QuantityPerOuter from
[Warehouse].StockItems A Left join Quantity_outer B on
(A.StockItemID=B.StockItemID) Order by QuantityPerOuter Desc

Obtaining this

Supplies to follow this tutorial

To follow and run the instructions in this tutorial, you must have the
following programs installed

• SQL-Server
• SSMS
• WideWorldImporters
  DB

Sometimes it is necessary to delete a table that is no longer in use.

To do this, use the following DROP TABLE statement, depending on the
syntax:

DROP TABLE [IF EXISTS] [database_name.] schema_name.]table_name;

In the above syntax you will find the following:

First, it specifies the name of the table to be dropped.

Second, it specifies the name of the database in which the table was
created and the name of the schema to which the table belongs.

IMPORTANT: The database name is optional, if you omit it, the DROP
TABLE statement will drop the table in the currently connected database.

Third, it uses the IF EXISTS clause to drop the table only if it exists.

The IF EXISTS clause is supported as of SQL Server 2016 13.x.

If you delete a table that doesn’t exist, you’ll get an error.

The IF EXISTS clause conditionally deletes the table if it already
exists.

When SQL Server drops a table, it also removes all data, triggers,
restrictions, and permissions from that table.

Also, SQL Server does not explicitly drop views and stored procedures
that reference the dropped table.

Therefore, to explicitly drop these dependent objects, you must use the
DROP VIEW and DROP PROCEDURE statements.

Additionally, SQL Server allows you to drop multiple tables at once using
a single DROP TABLE statement as follows:

DROP TABLE [database_name.][schema_name.]table_name_1,
[schema_name.]table_name_2, …
[schema_name.]table_name_n;

Examples

Delete a table that does not exist

The following statement deletes a table named revenues in the sample
database sales schema:

DROP TABLE IF EXISTS sales.revenues;

It is evident that no table or record will be affected, since it does not
exist. This, thanks to the IF EXISTS condition.

Example of deleting a single table

The following statement creates a new table with CREATE TABLE named
flights in the sales schema:

CREATE TABLE transit.flights (
    flight_id INT PRIMARY KEY,
    flight_note VARCHAR (255) NOT NULL,
    flight_date DATE NOT NULL
);

To remove the flight table, use the following statement:

DROP TABLE transit.flight;

Drop a table with a foreign key constraint example

The following statement creates two new tables named airlines_groups and
suppliers in the airport schema:

CREATE SCHEMA airport;

CREATE TABLE `airlines` (
  `airgroup_id` int(11) NOT NULL,
  `airgroup_name` varchar(50) NOT NULL,
  PRIMARY KEY (`airgroup_id`)
) ;

CREATE TABLE `suppliers` (
  `supplier_id` int(11) NOT NULL,
  `supplier_name` varchar(50) NOT NULL,
  `group_id` int(11) NOT NULL,
  PRIMARY KEY(`supplier_id`),
  KEY `airgroup_id_idx` (`group_id`),
  CONSTRAINT `airgroup_id` FOREIGN KEY (`group_id`) REFERENCES
`airlines` (`airgroup_id`));

Try to drop the suppliers table:

DROP TABLE airport.suppliers;

SQL Server returns the following error:

‘Cannot drop object ‘airport.suppliers’. There is a reference to it
in a FOREIGN KEY constraint’

SQL Server does not allow you to drop a table that is referenced by an
external constraint.

To drop this table, you must first drop the foreign key constraint or the
reference table.

In this case, you must first drop the foreign key constraint on either
the suppliers table or the suppliers table before dropping the
supplier_groups table.

DROP TABLE airport.suppliers_group;

DROP TABLE airport.suppliers;

If you use a single DROP TABLE statement to drop both tables, the
reference table must come first as shown in the query below:

DROP TABLE airport.suppliers, airport.supplier_groups;

Supplies to follow this tutorial

To run the examples featured here, you must have the following software
packages:

• MySQL Server
• MySQL Workbench

In this tutorial we will take a walk through various concepts that are
essential when working with SQL, in any of its versions.

Primary Key

A PRIMARY KEY is a column or group of columns that uniquely identifies
each row in a table.

You can create a primary key for a table using the PRIMARY KEY
constraint.

No two rows in a table can have the same primary key.

Examples of primary keys are NID (associated with a person), passport
number, a MAC address, a public IP address, or ISBN (associated with a
book).

Telephone directories and dictionaries cannot use names or words or Dewey
decimal numbers as candidate keys, because they do not uniquely identify
telephone numbers or words.

Primary keys are used with the SQL standard primarily for programmer
convenience.

The primary key allows the relationships of the table that has the
primary key with other tables that are going to use the information of
this table, this, for an entity-relationship model.

Each table can contain only one primary key.

All columns participating in the primary key must be defined as NOT NULL.

However, SQL Server automatically sets the NOT NULL constraint for all
primary key columns if the NOT NULL constraint is not specified for these
columns.

SQL Server also automatically creates a unique clustered index (or
nonclustered index if specified as such) when it creates a primary key.

Ways to create a primary key

Primary keys can be specified when the table is created (using CREATE
TABLE) or by changing the existing structure of the table (using ALTER
TABLE).

You can also use the tools of each SQL database manager, for example SSMS
or MySQL Workbench.

The syntax for each type of Database is as follows:

MySQL-MariaDB:

CREATE TABLE transit.passenger
(PassNUMB integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (PassNUMB));

SQL-Server:

CREATE TABLE transit.dbo.passenger
(PassNUMB integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

In this Passenger table, the PassNUMB column is the primary key column.

It means that the PassNUMB column contains unique values.

Table with two columns as a primary key

The following statement creates a new table named sales.participants
whose primary key consists of two columns:

CREATE TABLE cohort.students(subject_id int,  student_id
int,  PRIMARY KEY(subject_id, student_id));

In this example, the values ​​in the subject_id or student_id column can
be duplicates, but each combination of values ​​in both columns must be
unique.

Normally, a table always has a primary key defined at creation time.

When an already created table does not contain a primary key

However, sometimes an existing table may not have a defined primary key.

In this case, you can add a primary key to the table using the ALTER
TABLE statement.

The following statement creates a table without a primary key:

CREATE TABLE academy.events(event_id INT NOT NULL, event_name
VARCHAR(255), start_date DATE NOT NULL,  durationDEC(5,2));

To make the event_id column the primary key, use the following ALTER
TABLE statement:

ALTER TABLE academy.dbo.events ADD PRIMARY KEY(event_id);

It is important to consider that if the academy.events table already has
data, before modifying the event_id column as the primary key, you must
ensure that the values ​​in event_id are unique.

FULL OUTER JOIN returns a result set that includes rows from the left and
right tables.

When no matching rows exist for the left table row, the right table
columns will have nulls.

Similarly, when there are no matching rows for the right table row, the
left table column will have nulls, other clauses you can use to do joins
between tables are INNER JOIN, LEFT JOIN, and RIGHT JOIN.

Here is a SELECT using FULL OUTER JOIN when joining two tables Table1 and
Table2:

SELECT  select_list FROM Table 1 FULL OUTER JOIN Table2 ON
join_predicate;

The OUTER keyword is optional, so you can omit it as shown in the
following query:

SELECT select_list FROM Table 1 FULL JOIN Table2 ON join_predicate;

The above means:

First, the left table Table1 is specified in the FROM clause.

Second, the table Table2 on the right and a join predicate are specified.

The following diagram illustrates a FULL OUTER JOIN of two sets:

FULL OUTER JOIN example

For this example, you need to have SQL Server, express edition installed,
although any edition is useful, as well as SSMS, and you also need to
download and install the WideWorldImporters database.

At the end of this article you will find the links to download each
element used here.

Two tables will be taken as samples that contain a common field, such as
the purchase order ID, to show the Stock ID of each item, the description
and the purchase order date.

To make that JOIN, the following statement is used:

SELECT Purchasing.PurchaseOrderLines.StockItemID,
Purchasing.PurchaseOrderLines.Description,
Purchasing.PurchaseOrders.OrderDate FROM Purchasing.PurchaseOrderLines
FULL OUTER JOIN Purchasing.PurchaseOrders ON
PurchaseOrderLines.PurchaseOrderID = PurchaseOrders.PurchaseOrderID;

In this example, the query returned the items that appear in the purchase
order table, and also in the purchase order line table, below are the
results:

8367 records were obtained almost instantaneously, since the FULL OUTER JOIN
instruction works with few computer resources.

Download links

• SQL-Server
• SSMS
• WideWorldImporters

SELF JOIN allows you to join a table to itself.

It is useful for querying hierarchical data or comparing rows within the
same table.

SELF JOIN uses the INNER JOIN or LEFT JOIN clause.

Because the query using the SELF JOIN references the same table,
the table alias is used to assign different names to the same table within
a query.

Note that referencing the same table more than once in a query without
using table aliases will result in an error.

Here is a SELECT with the syntax of joining table T with itself:

SELECT select_list FROM T t1 [INNER | LEFT] JOIN T t2 ON
join_predicate;

The query references table T twice. The table aliases t1 and t2 are used
to give table T different names in the query.

SQL Server Self Join Example

Consider the following table of staffs from the sample database:

Using SELF JOIN to query hierarchical data

The student table stores the name information of each student, as well as
its identification number.

It also has a column called idSupervisor that specifies
the direct academic supervisor. For example, Ann reports to supervisor
Christopher.

Cristopher has no supervisor, so the supervisor id column has a NULL.

To get who reports to whom, we will use SELF JOIN as shown in the
following query:

SELECT * FROM dbo.student AS S1 INNER JOIN dbo.student AS S2 ON
S1.idStudent = S2.idSupervisor;

And the result would be the following:

In this example, the student table was referenced twice.

The union predicate coincides with the relationship between student and
supervisor, in the understanding that the most outstanding students are
supervisors.

The S1 column does not have Ann or Bea due to the INNER JOIN
effect.

If you replace the INNER JOIN clause with the LEFT JOIN
clause as shown in the following query, you will get the result set that
includes all the records.

SELECT * FROM dbo.student AS S1 LEFT JOIN dbo.student AS S2 ON
S1.idStudent = S2.idSupervisor;

In this tutorial, you have learned how to use MySQL Server’s SELF
JOIN to query hierarchical data in a single table.

The RIGHT OUTER JOIN statement joins the values ​​from the first table
with the values ​​from the second table.

It will always return the rows from the second table, even if they don’t
meet the condition.

That is, if a row from the right table has no matching rows from the left
table, the left table column in the result set will have nulls, other
clauses you can use to do joins between tables are INNER JOIN and LEFT
JOIN.

The syntax is as follows:

SELECT * FROM table1 RIGHT JOIN table2 WHERE table1.column1 =
table2.column1

In this syntax, table1 is the left table and table2 is the right table.

Note that RIGHT JOIN and RIGHT OUTER JOIN are the same.

The OUTER keyword is optional.

Use a RIGHT JOIN operation to create a right outer join.

SQL Server RIGHT JOIN Example

We will use the sales.order_items and production.products table from the
sample database for the examples.

SELECT  Purchasing.SupplierTransactions.TransactionAmount,
Purchasing.SupplierTransactions.TransactionDate,
Purchasing.SupplierCategories.SupplierCategoryName
FROM Purchasing.Suppliers
RIGHT outer JOIN Purchasing.SupplierTransactions ON
Purchasing.Suppliers.SupplierID =
Purchasing.SupplierTransactions.SupplierID
INNER JOIN Purchasing.SupplierCategories ON
Purchasing.Suppliers.SupplierCategoryID = 2
ORDER BY NEWID()

Which yields the following result:

In this tutorial, you have learned how to use SQL Server’s RIGHT JOIN to
query data from two tables.

LEFT OUTER JOIN allows reading with table joins that exclude the table
indicated on the left from the intersection conditions.

This article tries to teach you how to apply this statement in SQL read
statements.

For this, you need to have MS SQL Server and SSMS.

Syntax

SELECT columnName(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.columnName=table2.columnName;

Effect

The JOIN syntax is a recursive join expression.

A JOIN expression is made up of a left side and a right side,
joined together using, in this case, LEFT [OUTER] JOIN.

A join expression can be an inner join (INNER) or an outer join (LEFT
OUTER).

Each join expression can be enclosed in parentheses.

On the left side, you can specify a transparent database table, or a join
expression join.

On the right hand side, a single transparent database table must be
specified, along with the join conditions after ON.

Clustered and pooled tables cannot be joined using join expressions.

As a practical application, the SQL WideWorldImporters Database will be
used.

And the SQL query that applies directly in this case is:

SELECT TOP (15) Purchasing.Suppliers.SupplierName,
Purchasing.SupplierTransactions.PaymentMethodID
FROM Purchasing.Suppliers LEFT OUTER JOIN
Purchasing.SupplierTransactions
ON Purchasing.Suppliers.SupplierID =
Purchasing.SupplierTransactions.SupplierID

Obtaining as a result the following:

You will now see all the rows in the Purchasing.Suppliers table, which is
the table on the left, being displayed as many times as there are matches
on the right side. ‘Nod Publishers’ has not made any transactions, so null
is returned.

JOIN in SQL are used to combine rows from two or more tables
based on a common field between them, thus returning data from different
tables.

A JOIN occurs when two or more tables are joined in one SQL
statement.

For example, INNER JOIN, Returns all rows when there is at least
one match in both tables.

LEFT JOIN Returns all rows from the left table, and matching rows
from the right table.

RIGHT JOIN Returns all rows from the right table, and matching
rows from the left table.

OUTER JOIN, Returns all the rows of the two tables, the left and
the right. Also called FULL OUTER JOIN.

However, this article focuses primarily on the INNER JOIN
condition.

INNER JOIN

The INNER JOIN clause searches for matches between rows in the product
and category tables.

If a row or record in the products table has the same value in the column
as a row in the categories table, the query combines the values ​​of the
columns specified in the SELECT list into a new row and includes
that new row in the result set.

It is the most common type of JOIN.

SELECT columnName(s) FROM table1 INNER JOIN table2 ON
table1.columnName=table2.columnName;

In mathematical terms, it is nothing more than the union of two sets that
have elements in common, so that if we make a diagram, it would look like
this:

The example to be used will be in the WideWorldImporters Database,
with the Purchasing.Suppliers and Purchasing.SupplierTransactions tables,
it will seek to obtain the name of the supplier and the amount of the
transaction, where the IDs in both tables match.

SELECT Purchasing.Suppliers.SupplierName,
Purchasing.SupplierTransactions.PaymentMethodID FROM
Purchasing.Suppliers JOIN Purchasing.SupplierTransactions ON
Purchasing.Suppliers.SupplierID =
Purchasing.SupplierTransactions.SupplierTransactionID

And the following result is obtained:

It is also possible to obtain a specific result for each supplier, giving
the identifier number, for example:

SELECT Purchasing.Suppliers.SupplierName,
Purchasing.SupplierTransactions.TransactionAmount,
Purchasing.SupplierTransactions.TransactionDate FROM
Purchasing.Suppliers INNER JOIN Purchasing.SupplierTransactions ON
Purchasing.Suppliers.SupplierID = 4

Where you can see that you want to obtain the amounts of the transaction
and the corresponding dates, from the supplier identified as 4 (Fabrikam,
Inc.).

INNER JOIN on three Tables

It is also possible to get data from three tables, using the INNER JOIN
clause, twice, as follows:

The following statement uses two INNER JOIN clauses to query data from
all three tables:

• Purchasing.Suppliers
• Purchasing.SupplierTransactions
• Purchasing.SupplierCategories

SELECT TOP(20) Purchasing.Suppliers.SupplierName,
Purchasing.SupplierTransactions.TransactionAmount,
Purchasing.SupplierTransactions.TransactionDate,
Purchasing.SupplierCategories.SupplierCategoryName
FROM Purchasing.Suppliers
INNER JOIN Purchasing.SupplierTransactions ON
Purchasing.Suppliers.SupplierID =
Purchasing.SupplierTransactions.SupplierID
INNER JOIN Purchasing.SupplierCategories ON
Purchasing.Suppliers.SupplierCategoryID = 2
ORDER BY NEWID()

Obtaining the following result:

The CROSS JOIN joins each row of the first table with each row of
the second table.

In other words, the cross join returns a cartesian product of rows from
both tables.

Unlike INNER JOIN or LEFT JOIN, CROSS JOIN does
not establish a relationship between the joined tables.

Suppose table 1 contains three rows 1, 2 and 3 and table T2 contains
three rows A, B and C.

CROSS JOIN gets a row from the first table (T1) and then creates a
new row for each row from the second table (T2). It then does the same
thing for the next row of the first table (T1) and so on.

MySQL Server CROSS JOIN Examples

The following statement returns the combinations of all Students and
Tests.

SELECT name, note FROM alumni.ma1b02 CROSS JOIN
alumni.evaluations_notes ORDER BY note;

Obtaining the following result:

As can be seen, a Cartesian product of the records in Table1 with the
records in Table2 is obtained.