PLC Programming

What are SQL Inner Join, And, Or, Having and Between Clause Statement Language Elements? (PART 5 of 8)

Learn about some of the more advanced SQL statements.

Listen to this article

In the previous article, What are the SQL Order By Clause and Expression statement language elements?, we learned about the structure of SQL defining the clauses, expressions defining the overall statement. Also, we used the Select statement to retrieve the last names in descending order, using the Order By clause. In this article, you will learn about some of the more advanced SQL statements and clause commands such as Inner Join, AND, OR, Having, and Between. We will also learn more about the SQL syntax or rules of the SQL language.

Remember, our sample database used in this series of articles was named realparsmodel.

Also, With the Entity Relationship Diagram, you can see the realparsmodel database has several tables that are related to one another.

Here is our last SQL statement that we have written in the SQL Query Tab, using the Order By clause.

‍

In general, the Order By clause will be used along with the Select statement to provide a descending or ascending listing of the results provided by the query.

AND Operator

The AND operator is a logical operator that combines two or more Boolean expressions and returns true only if both expressions evaluate to true.

Now using the SELECT statement from the Students table where the results display only records having the country of USA AND the state of California AND having a Credit limit greater than $100,000, the query would be written like this:

‍

Press the Execute Query button to run the statement. By doing this, it shows a student from California with a large credit as a result.

OR Operator

The OR operator is a logical operator that combines two or more Boolean expressions and returns true when either condition is true.

Now using the SELECT statement from the Students table, where the results display records having the country of USA OR the country of France, the query would be written like this:

‍

Once again, Press the Execute Query button to run the statement. By doing this, it shows a list of the students from the USA or France as a result.

BETWEEN Operator

The BETWEEN operator is a logical operator that allows you to specify whether a value is in a range or not.

Now using the SELECT statement from the Courses table where the results display records having a price BETWEEN 90 and 100, the query would be written like this:

‍

To run this SQL statement, Press the Execute Query button. By doing this, it shows the records of the price range between 90 and 100 as a result.

INNER JOIN Clause

The INNER JOIN clause matches rows in one table with rows in other tables and allows you to query rows that contain columns from both tables.

For example, you can join tables that have key relationships like the Courselines and Courses tables.

We want to get the Coursecode and Coursename from the Courses table and the TextDescription of course lines from the Courselines table.

To do this, write the SQL statement to select data from both tables by matching rows based on the Course line columns, using the INNER JOIN clause as follows:

‍

By pressing the Execute Query button, it shows the records of Course code, Course name and text description columns.

HAVING Clause

The HAVING clause is used in the SELECT statement to specify filter conditions for a group of rows or aggregates.

Now the department asks us to write the SQL statement, using the HAVING clause from the Order details table, with results displaying the Order number column data with two calculated columns for the quantity and total amount paid for each order only having totals greater than 1000 and quantity greater than 600.

SELECT OrderNumber, SUM(QuantityOrdered) AS ItemsCount,SUM(PriceEach*QuantityOrdered) AS TotalFROM realparsmodel.OrderdetailsGROUP BY OrderNumberHAVING Total > 1000 AND ItemsCount > 600;

‍