MariaDB Select Statement- Detailed Guide

In this MariaDB tutorial, we will study the use of the MariaDB Select statements and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB select
• MariaDB select database
• MariaDB select query example
• MariaDB select top 10
• MariaDB select for update example
• MariaDB select for update
• MariaDB select distinct
• MariaDB select count
• MariaDB select all
• MariaDB select as
• MariaDB select all tables
• MariaDB select all columns except one
• MariaDB select all users
• MariaDB select a database
• MariaDB select as Name
• MariaDB select by id
• MariaDB select order by
• MariaDB select count distinct
• MariaDB select duplicates
• MariaDB select database version
• MariaDB select exists
• MariaDB select except
• MariaDB select empty
• MariaDB select first row
• MariaDB select from select
• MariaDB select from subquery
• MariaDB select from dual
• MariaDB select group by
• MariaDB select having
• MariaDB select without header
• MariaDB select last 24 hours
• MariaDB select last row
• MariaDB select last 10 rows
• MariaDB select last_insert_id()
• MariaDB select left join
• MariaDB select max
• MariaDB select multiple columns
• MariaDB select max date
• MariaDB select multiple tables
• MariaDB select most recent record
• MariaDB select max row
• MariaDB select min
• MariaDB select max id
• MariaDB select null
• MariaDB select not null
• MariaDB select no cache
• MariaDB select not in
• MariaDB select nth row
• MariaDB select output to file
• MariaDB select one row
• MariaDB select random rows
• MariaDB select without table

MariaDB Select Statement

In this section, we will learn how to use the MariaDB SELECT statement with the help of syntax and examples.

In MariaDB, the SELECT statement is used to recover records from one or more tables in the MariaDB. The simplest syntax form of the SELECT statement of the table is given below:

SELECT EXPRESSIONS FROM TABLE_NAME WHERE [CONDITIONS];

The full syntax form of the SELECT statement is given below:

SYNTAX:

SELECT [ ALL | DISTINCT ]
expressions
FROM tables
[WHERE conditions]
[GROUP BY expressions]
[HAVING condition]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT [offset_value] number_rows | LIMIT number_rows OFFSET offset_value]
[PROCEDURE procedure_name]
[INTO [ OUTFILE 'file_name' options 
       | DUMPFILE 'file_name'
       | @variable1, @variable2, ... @variable_n ]
[FOR UPDATE | LOCK IN SHARE MODE];

The syntax explanation:

• ALL: The ALL constraints returns all matching rows of the table.
• DISTINCT: It removes the duplicate from result set from the table.
• EXPRESSIONS: The colun or calculations that we want to regain. If we can use * expressions (asterik) from to call all rows from the table.
• TABLES: The table from which we want to retreive all rows. There should be atleast one table from FROM clause.
• WHERE conditions: [Optional] By using the WHERE condition, it must met a records to be selected.
• GROUP BY expressions: [Optional]. It collects data from multiple records and group the select by one or more than one columns.
• HAVING condition: [Optional]. It is used in union with GROUP BY condition to restrict the grouped rows to those whose condition turn out to be TRUE.
• ORDER BY expression: [Optional]. It is used to arrange the records in ascending or descending by using the ASC or DESC constraint in the result set.
• LIMIT: [Optional]. If the LIMIT clause is used in the statment then it controls the maximum results to be retrieve.
• PROCEDURE: The procedure_name is the name of the procedure that process the data in the result set,
• INTO: It allows us to write the result set to a file or variable.
• FOR UPDATE: Until the transaction has completed, the records will be affected by the query are write_locked.
• LOCK IN SHARE MODE: The records can be affected by query by their transcations but it will not affected by update or delete those other transcations.

The sample example of using the SELECT statement is given below:

EXAMPLE:

The example to select all the rows from the Teacher_Activities Table is given below:

SELECT * FROM TEACHER_ACTIVITIES;

As we see in the above image, we have retrieved all rows from the Teacher_Activities table by using the SELECT statement.

Read: MariaDB Join with Examples

MariaDB Select Query Examples

In this section, we will see the syntax and the example of the MariaDB SELECT query. As we know that the SELECT statement is used to retrieve one or more rows of information from the table.

The syntax of the SELECT query is given below:

SYNTAX:

SELECT expressions FROM TABLE_NAME where [CONDITIONS];

The sample example of the SELECT query is given below:

First, let us show you all the columns from the USA_Basketball Table by using the SELECT statement.

SELECT * FROM usa_basketball;

As we see in the above image, we have retrieved all columns from the USA_BasketBall table by using the asterisk expression (*) before the FROM clause inside the SELECT statement.

EXAMPLE 1: Selecting the multiple columns from the USA_BasketBall Table by using the SELECT statement:

SELECT player_name,current_age FROM USA_BASKETBALL;

As we see in the above image, we have selected the PLAYER_NAME, CURRENT_AGE columns from the USA_BasketBall table by using the SELECT query.

EXAMPLE 2: Let’s use the WHERE conditions with the SELECT statement in the USA_BasketBall table.

SELECT * FROM USA_BASKETBALL WHERE CURRENT_AGE>=25;

• As we see in the above image, we have used the WHERE condition with >= operator to use as a condition and to check the age of the player in the Current_Age column.
• And we have retrieved all rows from the USA_Basketball table by using the asterisk expression in the SELECT statement.
• So it provided 4 rows by using the WHERE condition with the SELECT statement with the help of >= operator as (greater or equal sign operator).

EXAMPLE 3: Let’s use the DISTINCT clause with the SELECT statement in the USA_BasketBall Table.

SELECT DISTINCT(Current_Age),player_name FROM usa_basketball;

• As we see in the above image, we have used the SELECT query in the usa_basketball table with the distinct clause on the Current_Age column and also called the player_name column from the table.
• With the help of a distinct clause, it called all unique ages from the Current_Age column along with the player_name column.
• As the result, we have retrieved 8 rows from the Usa_basketball table with the help of a distinct clause on the current_age column along with player_name as the output.

Read: MariaDB Set Variable – Complete Guide

MariaDB Select a Database

In this section, we will learn how to select a database in MariaDB with the syntax and examples.

The USE database_name refers to the current database for the following statement expressed in the MariaDB. The database statement remains the default until another USE statement has been issued.

The syntax of the SELECT Database is given below:

SYNTAX:

USE DATABASE_NAME;

The sample example of the SELECT database is given below:

Let’s have a quick look at all databases by using the below query:

SHOW DATABASES;

As we see in the above image, we have shown all databases by using the SHOW statement for all databases created by the user as per requirement.

USE AIRBNB_DB;

As we see in the above image, we have selected the database as per the user’s choice and used it for some expressions on the table after using the USE statement.

Read: MariaDB Select Into + Examples

MariaDB Select Top 10

In this section, we will learn how to select the first 10 rows from the table by using the LIMIT clause in MariaDB with the syntax and example.

In MariaDB, the LIMIT clause is used to retrieve records from the table and the number of records will be returned based on the limit value. The syntax of the SELECT TOP 10 statement is given below:

SELECT EXPRESSIONS FROM TABLE_NAME WHERE [CONDITIONS] 
[ORDER BY  EXPRESSION [ASC | DESC]]
LIMIT ROW_COUNT;

The syntax explanation:

• expressions: The column or expressions that we have to retreive.
• tables: The tables that we want to retreive records from. There must be atleast one table from the FROM clause.
• conditions: The conditions that to be met from which records to be selected.
• ORDER BY expression: It must be used in the SELECT LIMIT statement so that we can order a result and target those result that we want to see as a return.
• LIMIT row_count: Specified a limited number of rows to be get as a result on row_count.

The sample example of the SELECT TOP 10 statement is given below:

Let’s first create and insert data in the WALMART table by using the INSERT and the CREATE TABLE statement is given below:

CREATE TABLE WALMART(
product_id INT PRIMARY KEY AUTO_INCREMENT,
Product_Name VARCHAR(50),
Product_Price FLOAT);

INSERT INTO WALMART (product_name,product_price) 
VALUES('BADDDASH T-Shirt',19.95),
('Vintage US American Flag T-shirt',14.95),
('EA FIFA 22',34.29),
('SIMS 4',23.99),
('Premium Beauty HairCare',200.00),
('Mellani Bed-Sheet',13.97),
('Modenna Face Mask Disposable',12.74),
('Derro Space Heaters',67.49),
('Annn Klein Watch',600.00),
('FitBit Charger',119.95),
('IT TAKES TWO XBOX Game',95.99),
('WAITIEE Wireless Charger',33.14),
('GrandeULips Cosmetic',216.00),
('PurePremium Blood Pressure Support',19.52),
('GLOBALWIN Ankle Boat Shoe for Women',13.99),
('PillowCase for Hair and Skin',12.74),
('Acer Inspire 5 Laptop',312.45);

SELECT * FROM walmart;

EXAMPLE:

SELECT * FROM walmart ORDER BY product_price DESC LIMIT 10;

As we see in the above image, we have limited only the top 10 products based on the product_price column in descending order.

So, by using the SELECT statement we have got the top 10 products inside the product_id, product_name, and product_price column in descending order but in a synchronized way.

Read: MariaDB regexp + Examples

MariaDB Select for Update

In this section, we will learn how to use the MariaDB SELECT FOR UPDATE statement with the syntax and examples.

In MariaDB, the SELECT FOR UPDATE statement allows us to lock the set records by using the SELECT statement. In that period, we can’t make any changes in the records to use the statement. It has the same locks searched for the MariaDB UPDATE statement which is used to set the rows.

The syntax of the MariaDB SELECT FOR UPDATE is given below:

SYNTAX:

SELECT * FROM TABLE_NAME
WHERE [CONDITIONS] FOR UPDATE;

The syntax explanation:

• SELECT statement: The select statement will give us the output result.
• TABLE_NAME: The tables that we want to retreive records from. There must be atleast one table from the FROM clause.
• conditions: The conditions that to be met from which records to be selected.
• FOR UPDATE: The columns which we want to update.

The sample example of the MariaDB SELECT FOR UPDATE statement is given below:

EXAMPLE:

SELECT * FROM usa_basketball WHERE current_age>=23 FOR UPDATE;

As we see in the above image, we have selected all columns from the usa_basketball table by using the SELECT statement with an asterisk sign (*) before the FROM clause and use as the condition to find the current_age column is greater than or equal to 23 with >= operator and update it by using the FOR UPDATE clause.

As the result, we have all details of all columns with 5 rows as the output by using the SELECT FOR UPDATE statement.

Read: MariaDB Row_Number Tutorial

MariaDB Select for Update Example

In this section, we will see another example related to MariaDB SELECT FOR UPDATE statement.

The syntax of the SELECT FOR UPDATE statement is given below:

SYNTAX:

SELECT EXPRRESSIONS FROM TABLE_NAME 
WHERE [CONDITIONS]
FOR UPDATE;

The sample example of MariaDB SELECT FR UPDATE statement is given below:

EXAMPLE:

SELECT player_name,nba_team FROM usa_basketballteam 
WHERE current_age<=23 FOR UPDATE;

In this image, we have used the FOR UPDATE clause to update the player_name and nba_team column from the SELECT statement.

And it is done based on the condition by using the WHERE clause to check the current_age column if it contains an age value less than or equal to 23 and to produce the output based on the result set.

As the result, we have details of player_name and nba_team by using the SELECT statement and the provide the output as 6 rows by using the FOR UPDATE clause with the help of the WHERE condition.

Read: MariaDB on Duplicate Key Update

MariaDB Select Distinct

In this section, we will learn how to use the MariaDB DISTINCT clause with the SELECT statement with the given syntax and examples.

The DISTINCT clause is used to remove the duplicate things from the result in the SELECT statement. The syntax of the DISTINCT clause in the SELECT statement is given below:

SYNTAX:

SELECT DISTINCT [expressions] from TABLE_NAME WHERE [CONDITIONS];

The syntax explanation:

• expressions: the columns that we want to retreive.
• TABLE_NAME: The table we want to retreive the rows from. There should be atleast one column in the FROM clause.
• WHERE conditions: [OPTIONAL]. The condition that mets for the records to be selected.

Note, while applying the DISTINCT clause in the SELECT statement of the table:

• When one expression is provided in the DISTINCT clause, the query will return unique vale of that expression.
• When one or more is provided in the DISTINCT clause, the query will return the combination of multiple expressions listed.
• In MariaDB, the DISTINCT clause doesn’t ignore the NULL values so during the result set it provides NULL value as the set value.

The sample example of the DISTINCT clause in the SELECT statement is given below:

Let’s have a quick look at all columns of the USA_basketballteam table by using the SELECT statement:

SELECT * FROM USA_basketballteam;

As we see in the above image, we have retrieved all columns data from the usa_basketballteam table by using the SELECT statement.

EXAMPLE 1: SINGLE EXPRESSION

Let’s have a look at how to use the distinct clause to remove duplicate values from a single expression by using the SELECT statement in MariaDB:

SELECT DISTINCT player_name FROM usa_basketballteam 
WHERE current_age>=21;

As we see in the above image, we have used the DISTINCT clause to remove duplicate rows from the USA_basketballteam table by using the >= operator in the SELECT statement.

By using the DISTINCT clause, we have got the output of 8 rows based on the condition for the current_age to check whether the age is greater than or equal to 21.

EXAMPLE 2: MULTIPLE EXPRESSIONS

Let’s have a look at how to use the DISTINCT clause to remove duplicates from more than one expression in the SELECT statement in MariaDB:

SELECT DISTINCT player_name,nba_team FROM usa_basketballteam 
WHERE current_age>=20;

• In this image, we have used the SELECT statement for the DISTINCT clause for multiple columns to remove duplicates from the USA_basketballteam table.
• And with the help of the WHERE clause as the condition to check the current_age for whose age is greater than or equal to 20.
• So as the result, 6 rows are given as the output for both player_name, nba_team columns in the usa_basketballteam table.

Read: MariaDB Window functions

MariaDB Select Count

In this section, we will learn how to use the MariaDB COUNT() function in the SELECT statement with the syntax and examples.

In MariaDB, the count function is used to return the count of an expression. The syntax of the COUNT() function in the SELECT statement is given below:

SYNTAX:

SELECT COUNT(AGGREGATE_EXPRESSION) FROM TABLE_NAME 
WHERE CONDITIONs [EXPRESSIONS];

The syntax explanation:

• aggregate_expression: In this column or expression where non-null values will be counted.
• table_name: The table from which we want to retreive data. And their should be atleast one column listed in the FROM clause in the table.
• WHERE conditions: There are the conditions that must be met for the reocrds to be selected.

The syntax example of the COUNT() function in the SELECT statement is given below:

Let’s have a quick review of all columns details by using the USA_BASKETBALLTEAM table by using the SELECT statement.

SELECT * FROM USA_BASKETBALLTEAM;

In this image, w have retrieved all column details by using the SELECT statement in the USA_BASKETBALLTEAM table.

EXAMPLES:

SELECT COUNT(Current_Age) as Current_Age from usa_basketballteam;

As we see in the above image, we have used the COUNT() function to calculate the current_age columns based on the rows and put them alias name as Current_Age column in the usa_basketballteam table.

Therefore, it showed 6 rows as the total count of the current_age column in the usa_basketballteam table.

Read: MariaDB Comment – How to use

MariaDB Select AS

In this section, we will learn how to use the MariaDB SELECT AS statement with the help of syntax and examples:

The AS keyword is known as ALIAS NAME which is used as a temporary name for columns or tables in the database.

DESCRIPTION:

MariaDB ALIAS is used to create temporary names for columns or tables.

• COLUMN HEADING: It is used in the result set which makes the user to read as a new name.
• TABLE HAEADING: It is used to shorten your MariaDB statement which makes it easier to read or when we are performing a self join (which means listing the table more than once in the FROM clause).

The syntax of the MariaDB SELECT AS statement is given below:

SYNTAX:

SELECT COLUMN_NAME AS ALIAS_NAME 
FROM TABLE_NAME; 

The syntax explanation:

• column_name: The original column name which we want to change into new_column_name by using the alias clause AS keyword.
• AS: [Optional] Whether we specify the AS keyword or it has no impact on the alias in MariaDB.
• alias_name: The temporary name provided to the original column in the result set.

NOTE:

• If the alias_name contains spaces then enclose it into the quotes.
• It is acceptable to use alias_name for the spaces in the column_name but is not the good practise to use spaces when we are aliasing the table_name as new alias_name.
• The alias_name is only valid within the scope of MariaDB statement.
• Most programmers will specify the alias_name while using in the column_name but not in the table_name.

Let’s have a quick view of the WALMART table by using the SELECT statement:

SELECT * FROM walmart;

In this image, we have retrieved all records by using the SELECT statement in the WALMART table.

The sample example of the SELECT AS statement is given below:

EXAMPLE:

SELECT product_name, COUNT(product_price) AS Count_price
FROM walmart
GROUP BY product_name;

As we see in the above image, we have selected the product_name and counted the product_price of each row by using the COUNT() function from the WALMART table. And we also grouped each row by the product_price column by its price using the GROUP BY clause.

So as the output, it grouped every item of product name from the product_name column by its single price from the product_price column in the WALMART table.

Read: MariaDB Date Function with Examples

MariaDB Select All Table

In this section, we will learn how to select all tables by using the MariaDB SHOW statement with the syntax and examples.

In MariaDB, the SHOW TABLES list the temporary table, sequences, and tables in the given database. With the help of the LIKE clause, we can arrange the table name in alphabetical order as per user choice when using the ‘%’ prefix or postfix sign to the table name as required.

The syntax of the SELECT ALL TABLES is given below:

SYNTAX:

SHOW [FULL] TABLES [FROM db_name]
    [LIKE 'pattern' | WHERE expr]

The sample example of the SELECT ALL TABLES is given below:

EXAMPLE 1:

SHOW TABLES;

In this image, we have used the SHOW TABLES which are inside the airbnb_db database.

EXAMPLE 2:

SHOW TABLES LIKE '%B';

In this image, we have used the LIKE clause to see the table name that ends with the B alphabet in the tables by using the SHOW TABLE statement.

NOTE:

In the LIKE clause we use the percentage sign
(%) as the prefix in the B alphabet then it will provide the all the tables starts with UpperCase or lowerCase based on the b/ B alphabet.But if we use the perfix or postfix percerntage (%) wiith the B alphabet then only it brings table name who has B in Uppercase as a table_name.

EXAMPLE 3:

SHOW TABLES LIKE '%b%';

In this image, we have used the LIKE clause to get the table name that contains the B alphabet between the table name. So as the output, it provided us with 6 table names with b alphabet in between them.

Read: MariaDB DateTime Tutorial

MariaDB Select All Users

In this section, we will learn how to use the MariaDB SELECT ALL USERS with the help of examples.

MariaDB SHOW USER is a MariaDB command which is responsible to show the list of all the users in the database server. So, the user records are saved with tables details along with the password which has user privileges with all information in it.

Hence running a query against the MySQL.user will return all users who are created inside the database and their information about these users.

The syntax of the SELECT ALL USERS is given below:

SEELCT USER FROM MYSQL.USER;

The syntax explanation:

• HOST: User host that can be localhost or %, etc.
• USER: name of the user such as ADMIN, ROOT or any specifc one, etc.
• PASSWORD: It denotes as the hash value as the password.
• Authentication_String: It denotes the security column.

In this image, we have gathered all user names by using the SELECT statement from Mysql.User server.

Read: MariaDB DATEDIFF Function

MariaDB Select All

In this section, we will learn how to use the MariaDB SELECT ALL statement with the syntax and examples.

The topic MariaDB select all refer to selecting all columns from the table. The SELECT statement is used to recover records from one or more tables in the MariaDB.

The syntax of the MariaDB SELECT ALL statement is given below:

SYNTAX:

SELECT * from TABLE_NAME;

The sample example of the SELECT ALL statement is given below:

EXAMPLE:

SELECT * FROM WALMART;

In this image, we have used an asterisk (*) sign for all columns from the SELECT statement. It means we have retrieved all columns from the WALMART table.

Read: MariaDB AUTO_INCREMENT + Examples

MariaDB Select All Columns Except One

In this section, we will learn how to select all columns except one in MariaDB with the given syntax and example.

The SELECT statement is used to retrieve all data from the table. To exclude certain columns from the table we can omit field names from the query.

Just suppose you have the WALMART table with the following columns.

SHOW FILEDS FROM WALMART;

To retrieve certain columns, you can name them in the SELECT statement is given below:

SELECT PRODUCT_ID,PRODUCT_NAME,PRODUCT_PRICE from WALMART;

The above query will retrieve product_id,product_name, and product_price from the WALMART table. But sometimes we want to retrieve all columns except one or two from the table.

To retrieve all columns except one, we can use the TEMPORARY TABLE statement on the existing table as a WALMART table.

Let’s start with the TEMPORARY TABLE:

So, Mysql temporary table is created for us during the current Mysql session. As the beneficial part if we can use it any time we want as long as we are in the current session ( not discontinued from MySql Server).

To create a temporary table from the existing table, we can use the CREATE TEMPORARY TABLE statement which is shown below:

CREATE TEMPORARY TABLE TEMP_WALMART AS select * from walmart;

In this above query, it will copy all details from the existing table as WALMART. Now we can use the ALTER TABLE statement to exclude a column from the SELECT statement.

The following statement will drop the PRODUCT_PRICE column from the TEMP_WALMART table and run the SELECT * statement for the temporary table.

ALTER TABLE TEMP_WALMART drop column product_price;

SELECT * FROM TEMP_WALMART;

If we need a record that stays after the end of our current session then we can use the Mysql View statement.

Read: MariaDB Create Sequence + Examples

MariaDB Select as Name

In this section, we will learn how to use MariaDB select aliases as Name Column with syntax and example.

MariaDB ALIASES is used to create a temporary table name or column name for the existing column in the table.

The syntax to MariaDB Select as Name is given below:

SELECT COLUMN_NAME AS ALIAS_NAME from TABLE_NAME;

First, let’s have a quick review of all columns from the WALMART table by using the SELECT statement which is given below:

SELECT * FROM WALMART;

The sample example to select as Name column in MariaDB is given below:

SELECT PRODUCT_NAME AS NAME from WALMART;

As we see in the above image, we have changed the product_name column to the NAME column for the temporary session by using the SELECT statement in the WALMART table.

Read: MariaDB Primary Key With Examples

MariaDB Select Order By

In this section, we will learn how to use the MariaDB Select Order By statement by using the syntax and examples.

Use the ORDER BY clause to order by resultset and returned it by the SELECT statement. We can specify just the column or expression with function. If we are using the GROUP BY clause, we can use the grouping function in the ORDER BY clause. Ordering is done after grouping.

The ORDER BY clause can be also be used to order the activities or a DELETE and UPDATE statement (usually with the LIMIT clause).

The syntax of the ORDER BY clause in the SELECT statement is given below:

SYNTAX:

SELECT * FROM TABLE_NAME order by column_name [asc|desc];

The sample of the SELECT ORDER BY statement is given below:

SELECT * FROM WALMART ORDER BY PRODUCT_PRICE DESC;

As we see in the above image, we have used the ORDER BY clause to arrange the product_price column in descending order by DESC keyword. So, it arranged and gave an output of all columns based on the product_price column from the SELECT statement in the WALMART table.

Read: MariaDB Delete Row + Examples

MariaDB Select Count Distinct

In this section, we will learn how to use the MariaDB Select Count Distinct statement by the given syntax and examples.

In MariaDB, the count() function is used to return the count of the expression and the distinct clause is used toused to remove the duplicate things from the result in the SELECT statement.

The syntax of the count() function in the SELECT statement is given below:

SYNTAX OF COUNT FUNCTION:

SELECT COUNT(aggregrate_expression) from TABLE_NAME
WHERE ONDITIONS;

The syntax of the distinct clause in the SELECT statement is given below:

SYNTAX OF DISTINCT CLAUSE:

SELECT distinct(expression) from TABLE_NAME
WHERE CONDITIONS;

Now, let’s try the COUNT function with the DISTINCT clause in the SELECT statement which is given below:

SYNTAX OF COUNT FUNCTION WITH DISTINCT CLAUSE:

SELECT COUNT( DISTINCT EXPRESSIONS) FROM TABLE_NAME
WHERE [CONDITIONS];

The sample example of the COUNT function with the DISTINCT clause in the SELECT statement is given below:

First, let’s create a USA_CostcaWholeSale table for a retailer shop by using the CREATE TABLE and the INSERT statement.

CREATE TABLE USA_CostcaWholeSale(
Product_Id INT PRIMARY KEY AUTO_INCREMENT,
Product_Name VARCHAR(100) NOT NULL,
Product_Price FLOAT);

INSERT INTO USA_CostcaWholeSale (Product_Name,Product_Price)
values('FireMan Fuel Tank Generator 7500W',899.99),
('Auburn Queen Medium Maitress',579.90),
('UpRight Vaccum With DuoClean PowerFins',70),
('Apple Watch Series 7 GPS',40),
('Apple Watch SE 40MM GPS',249.99),
('Penelope Garnic Sectional With Ottoman',1299.99),
('ProForm Trainer TreadMill',799.99),
('Apple MacBook Pro 14inches',1749.99),
('Theradome Laser Growth Hair',67.75),
('LifeTime Duster Fusion Boat',349.99),
('Microsoft Surface Laptop 14 inches',1299.99),
('Ruby and Diamond 14kt White Gold Necklace',799.99),
('Isadora Fabric Chase Sectional',2999.99),
('Sony UXH HD TV',1999.99),
('Arlo Pro 3 FloodLight Camera with Solar Panel',199.99),
('Seagate OneTouch Hard Drive 5TB',109.99),
('Mavorga De-Cafe Coffee',38.99),
('Voltaren Arthritis Pain Gel 12.34 Ounce Gel',38.99),
('PerserVision AERDs 2 Formula Gel',39.99),
('Kirkland Signature Walnut 2lbs',14.99);

SELECT * FROM USA_COSTCAWHOLESALE;

In this image, we have retrieved all the table information by using the SELECT statement for the USA_CostcaWholesale table.

EXAMPLE:

SELECT COUNT( DISTINCT PRODUCT_NAME) AS 'Total Products Count' 
FROM USA_COSTCAWHOLESALE
WHERE  product_price>=100.99;

As we see in this image, we have counted the product_name column by using the count distinct function which is based on the product_price column, and using greater than or equal to an operator as >= for 100.99. So, it calculated on the product_price and tells that there are 13 products name in the product_name column.

Read: MariaDB Cast with Examples

MariaDB Select Max

In this section, we will learn how to use the MariaDB select max function with the given syntax and examples.

In MariaDB, the max() function is used to return the maximum value of the expression.

The syntax of the MAX() function by using the SELECT statement is given below:

SYNTAX:

SELECT MAX( AGGREGATE_EXPRESSIONS) FROM TABLE_NAME
WHERE [CONDITIONS];

The syntax explanation:

• aggregate_expression: The column or expression from which the maximum value will be returned.
• table_name: the table from which we need to retreive records from. There should be atleast one table listend in the FROM clause.
• where conditions: These conditions should be met from which records to be selected.

The sample example to use the max() function in the SELECT statement is given below:

EXAMPLE – BY SINGLE EXPRESSION:

SELECT MAX(PRODUCT_NAME) from usa_costcawholesale
where product_price>=100.99;

In this image, we have maximized the product_name based on the product_price and put as the condition for the product_price column for the price is greater than or equal to the operator as 100.99 by using the WHERE clause. So as the result, it provided the product_name as ‘Sony UXH HD TV’.

It is the highest price in the product_name column with a price of 1,999.99 in the product_price column which is based on the max() function query as given above.

EXAMPLE 2-USING GROUP BY CLAUSE

Let’s look at how to use the GROUP BY clause with the MAX() function in the MariaDB.

If we are returning columns that are not summarized in the Max() function then we can use the GROUP BY clause.

SELECT product_id, MAX(product_name) AS " Max product Name"
FROM usa_costcawholesale
where product_price>=100.99
GROUP BY product_id;

In this image, we have maximized the product_name column as the ‘MAX PRODUCT NAME’ column by using the AS clause in the USA_costcawholesale table. To use the max() function and put the product_price column under the condition based on the price 100.99 which is greater than or equal to the operator and grouping is done based on their product_id column.

So as the output, it provided the 13 rows with product_id and product_name column based on the MAX() function in the SELECT statement in the USA_COSTCAWHOLESALE table which is shown above.

Read: MariaDB Substring [11 Examples]

MariaDB Select Min

In this section, we will learn how to use the MariaDB Select Min function with the given syntax and example.

In MariaDB, the MIN() function is used to return the minimum value of the expression from the table.

The syntax of the MIN() function in the SELECT statement is given below:

SYNTAX:

SELECT MIN(aggregrate_expression) from table_name
where [conditions];

The syntax explanation:

• aggregate_expression: The column or expression from which the minimum value will be returned.
• table_name: the table from which we need to retreive records from. There should be atleast one table listend in the FROM clause.
• where conditions: These conditions should be met from which records to be selected.

The sample example of the MIN() function in the SELECT statement is given below:

EXAMPLE: BY SINGLE EXPRESSION

SELECT MIN(PRODUCT_NAME) from usa_costcawholesale
where product_price>=100.99;

In this image, we have maximized the product_name based on the product_price and put as the condition for the product_price column for the price is greater than or equal to the operator as 100.99 by using the WHERE clause. So as the result, it provided the product_name as ‘Apple MacBook Pro 14 inches.

The product_name column ‘Apple MacBook Pro 14 inches’ has the lowest price of 1,749.99 in the product_price column which is based on the min() function query as given above.

EXAMPLE: GROUP BY CLAUSE

Now, let’s look at how to use the min() function with the group by clause in the SELECT statement. If we are returning the columns which are not encapsulated in the min() function then we can use the group by clause in the query.

SELECT product_id, MAX(product_name) AS " Max product Name"
FROM usa_costcawholesale
where product_price>=100.99
GROUP BY product_id 
LIMIT 5;

In this image, we have minimized the product_name column as the ‘Less Purchase Product Name:’ column from the USA_costcawholesale table by using the min() function. And to put the product_price column under the condition based on the price 2999.99 which is less than or equal to operator and grouping is done based on their product_id column.

So as the output, it provided the 5 rows with product_id and product_name column based on the MIN() function in the SELECT statement in the USA_COSTCAWHOLESALE table which is shown above.

NOTE:

The purpose of using the LIMIT clause is to provide the top 5 rows based on the query. As per the above query, by using the GROUP BY clause the output has giving many rows in the result. So, to minimise the result we haved used the LIMIT clause and bring the top 5 output as the result to make it understandable.

Read: MariaDB LIMIT + Examples

MariaDB Select Group By

In this section, we will learn how to use the MariaDB select group by clause with the syntax and example.

The SELECT statement in MariaDB uses the GROUP BY clause to collect data from numerous records and combine the results by one or more columns.

The syntax of the GROUP BY clause in the SELECT statement is given below:

SELECT EXPRESSION_1,EXPRESSION_2,
EXPRESSION_N,AGGREGATE_FUNCTION(EXPRESSION)
FROM TABLE_NAME
WHERE [CONDITIONS]
GROUP BY EXPRESSION_1, EXPRESSION_N;

The syntax explanation:

• expression_1, expression_2, expression_n: The expression that are not encapsulated in the aggregate_function can be used in the group by clause.
• aggregate_function: Any function can be used for expression such as MIN, MAX, AVG, SUM and COUNT function
• table_name: The table from which data can be retreived. And atleast one table should be mentioned in the FROM clause.
• where [conditions]: The condition from which record are set and to be selected.

The sample example of the GROUP BY clause in the SELECT statement is given below:

EXAMPLE: USING SUM() function

SELECT PRODUCT_NAME,SUM(PRODUCT_PRICE) AS 'TOTAL PRICE OF PRODUCT'
WHERE PRDOUCT_PRICE>=100
GROUP BY PRODUCT_NAME;

• In this image, we have selected the product_name and product_price columns and summed up the product_price column as the ‘Total Price of Product’ column by using the sum() function.
• It was done based on the condition on product_price as 100.99 by using the GREATER OR EQUAL SIGN operator and grouped it on the product_price column.
• As the result, by using the LIMIT clause it provided the top 5 products name with price in the product_name and product_price column in the usa_costcawholesale table by using the GROUP BY clause in the above query.

Read: MariaDB index with Examples

MariaDB Select Last_Insert_ID

In this section, we will learn how to use the MariaDB last_insert_id function with the SELECT statement. Also, we will illustrate syntax and examples.

In MariaDB, the last_insert_id() function returns the first auto_increment value that was set by the most recent INSERT or UPDATE statement which is affected the auto_increment column.

The syntax of the last_insert_id() function is given below:

SYNTAX:

LAST_INSERT_ID( EXPRESSIONS);

The sample example of the last_insert_id() function in the SELECT statement is given below:

SELECT LAST_INSERT_ID (PRDOUCT_ID) FROM USA_COSTCAWHOLESALE;

As we see in the above image, the last_insert_id() function will return 20th as the last row because the last INSERT statement inserted a product_id as 20 which was also an auto_increment clause.

Read: MariaDB LIKE Operator [7 Examples]

MariaDB Select Last 10 Rows

In this section, we will learn how to use the MariaDB to select the last 10 rows by using the syntax and example.

In MariaDB, to select the last 10 rows we can subquery the SELECT statement with the LIMIT clause. First, let’s see the all columns of the USA_COSTCAWHOLESALE table by using the SELECT statement.

SELECT * FROM USA_COSTCAWHOLESALE;

In this image, we have retrieved all columns from the USA_COSTCAWHOLESALE table by using the SELECT statement and there were 20 records in the table.

The syntax to get the last 10 rows from a table, we can use the LIMIT clause in the table:

SYNTAX:

SELECT * FROM (
   SELECT * FROM TABLE_NAME ORDER BY EXPRESSION_1 DESC LIMIT 10
)Var1
   ORDER BY EXPRESSION_1 ASC;

Now, let’s implement the above query by using the LIMIT clause:

EXAMPLE:

SELECT * FROM (
        SELECT * FROM USA_COSTCAWHOLESALE ORDER BY PRODUCT_ID DESC LIMIT 10
    )Var1
    ORDER BY PRODUCT_ID ASC;

As we see in the above image, we have got the last 10 rows of all 3 columns (product_id, product_name, and product_price) by using the LIMIT clause in the SELECT statement of the USA_COSTCAWHOLESALE table.

Read: MariaDB query examples

MariaDB Select Exists

In this section, we will learn how to use the MariaDB Select Exists condition with the given syntax and example.

In MariaDB, the Exist statement is used in combination with the query and it is considered “to be met” in the statement. If the statement should return at least a row as a resultset. It can be also used with the INSERT, SELECT, DELETE and UPDATE statements.

The syntax of the EXISTS statement in the SELECT statement is given below:

SYNTAX:

WHERE EXISTS [SUBQUERY];

The syntax explanation:

• subquery: Normally, the SELECT statement starts with the SELECT * rather than list of expressions or column name. MariaDB ignores the list of expressions in the subquery anyways.

NOTE:

MariaDB statement that normally uses the EXISTS conditions are every ineffiecent cause the subquery need to RE-RUN for every row in the outer’s table. The most efficient way of writing the subquery is that don’t use the EXISTS condition in the statement.

The sample example of the EXISTS condition in the SELECT statement is given below:

SELECT * FROM WALMART
 WHERE EXISTS 
(SELECT * FROM USA_COSTCAWHOLESALE
WHERE USA_COSTCAWHOLESALE.PRODUCT_ID= WALMART.PRODUCT_ID);

As we see in the above image with the help of the EXISTS statement, it will return all records from the WALMART table and at least one record from the USA_COSTCAWHOLESALE table with the matching product_id column.

Read: MariaDB Insert Into + Examples

MariaDB Select Last Row

In this section, we will learn how to use the MariaDB select last row by using the given syntax and example. To select the last row from the table, we can use the ORDER BY clause with DESCENDING ORDER property and put the LIMIT clause as 1.

First, let’s see all records of the USA_COSTCAWHOLESALE table by using the SELECT statement:

SELECT * FROM USA_COSTCAWHOLESALE;

So, our last record is with product_id as 20th, product_name as Kirkland Signature Walnut 2 lbs with the price of $14.99 of product_price column. To get the last query, we will use the following statement given below:

SELECT * FROM USA_COSTCAWHOLESALE
ORDER BY PRODUCT_ID DESC
LIMIT 1;

In this image, it fetched the last row of the USA_COSTCAWHOLESALE table with the product_id as 20, product_name as “Kirkland Signature Walnut 2lbs” and the product_price column as 14.99.

MariaDB Select Multiple Tables

In this section, we will learn how to select multiple tables in MariaDB using syntax and examples.

This SELECT statement is used to retrieve fields from multiple tables. To do so we can use the JOIN clause to get data from multiple tables.

The syntax of the JOIN clause in the multiple tables is given below:

SELECT table_name1.expression, table_name2.expression  
FROM table_name2  
INNER JOIN table_name1 
ON table_name2.expression = table_name1.expression  
 [conditions];

Let’s take two tables, one table of Walmart and another table of the USA_COSTCAWHOLESALE. First, let’s see the WALMART table by using the SELECT statement and then for the USA_COSTCAWHOLESALE table by using the same statement.

SELECT * FROM WALMART;

In this image, we have retrieved all the records from the WALMART table by using the SELECT statement.

SELECT * FROM USA_COSTCAWHOLESALE;

In this image, we have retrieved all records from the USA_COSTCAWHOLESALE table by using the SELECT statement.

The sample example to use the JOIN clause for the multiple tables is given below:

SELECT WALMART.PRODUCT_ID, usa_costcawholesale.PRODUCT_NAME
FROM WALMART 
INNER JOIN USA_COSTCAWHOLESALE  
ON WALMART.PRODUCT_ID = usa_costcawholesale.PRODUCT_ID
ORDER BY PRODUCT_ID;

• In this image, it took the product_id of the WALMART table and product_name of the USA_COSTCAWHOLESALE table as the output based on the ORDER BY clause.
• So, product_id of the USA_COSTCAWHOLESALE table worked as the foreign key of the WALMART causing both tables to have the product_id as a common column.
• So as the output, it provided the 17 records from both tables ( WALMART, USA_COSTCAWHOLESALE).

Read: How to Change Column in MariaDB

MariaDB Select Empty

In this section, we will learn how to use the MariaDB Select Empty by using the syntax and example.

In MariaDB, to check if a column is empty or NULL for that we can use the WHERE clause with IS NULL condition. If we can also use the column as ” “ i.e; empty column.

First, let’s create a USA_ IKEA table using the CREATE TABLE statement as given below:

CREATE TABLE USA_IKEA(
PRODUCT_ID INT AUTO_INCREMENT PRIMARY KEY,
PRODUCT_NAME VARCHAR(100),
PRODUCT_PRICE FLOAT);

INSERT INTO USA_IKEA (PRODUCT_NAME,PRODUCT_PRICE)
VALUES(' Skubbs BedSheet and Pillows',209.99),
('MatchSpel Gaming Chair',189.99),
('Lanespelare Mug and Mug Holder',29.99),
('Uppspel Pegboard', 19.99),
('MatchingSpel Chair',189.99),
('MALM 2-DRAWER Chest',49.99),
('HEMENES 2-DRAWER Chest',79.99),
('GlayDom Tray Table', 19.99),
('LACK Wall-Shelf',7.99),
('LillaGen Sink',98.00),
('OdenSvik Sink',110.00),
('HorVik CounterPart Sink',100.00);

SELECT * FROM USA_IKEA;

In this section, it retrieved all records from the USA_IKEA table by using the SELECT statement.

NOTE

Durning the inserton time while using the INSERT statement we need to use the NULL value as a value to keep the records empty of that row. As we have done in the USA_IKEA table.

The sample example of the IS NULL clause by using in the SELECT statement is given below:

SELECT * FROM USA_IKEA WHERE product_price IS NULL OR
product_price = ' ';

In this image, we have used the IS NULL constraint to get that row that has an EMPTY value or NULL value in the records. For that, we have used the SELECT statement in the USA_IKEA table.

Read: MariaDB Update Statement with Examples

MariaDB Select Except

In this section, we will learn how to use the EXCEPT clause in the MariaDB select statement with the help of syntax and examples.

In MariaDB, the except operator is used to compare the result set between two select statements and gives the output of distinct rows from the first select statement which are not the output of the second select statement.

In simple language, it subtracts the result set of queries from the other.

The syntax example of the EXCEPT operator is given below:

SYNTAX:

SELECT -STATEMENT 
EXCEPT 
SELECT - STATEMENT;

The syntax explanation:

• First, specify at least two SELECT statement from which we want to compare thier result sets.
• Secondly, use the EXCEPT operator between two SELECT statements.

The columns of the SELECT statement must have some rules that need to be followed:

• The number and order of columns must be same in the SELECT statement.
• The data type of the correponding column must be same.

The data type of corresponding columns of both tables ( WALMART, USA_IKEA) is given below:

CREATE TABLE WALMART(
PRODUCT_ID INT AUTO_INCREMENT PRIMARY KEY,
PRODUCT_NAME VARCHAR(50),
PRODUCT_PRICE FLOAT);

CREATE TABLE USA_IKEA(
PRODUCT_ID INT AUTO_INCREMENT PRIMARY KEY,
PRODUCT_NAME VARCHAR(50),
PRODUCT_PRICE FLOAT);

In this image, we have described columns in the WALMART table by using the DESC keyword.

In this image, we have described columns in the USA_IKEA table by using the DESC keyword.

The sample example of the EXCEPT operator in the SELECT statement is given below:

EXAMPLE:

First, let’s see all records of the WALMART table by using the SELECT statement is given below:

SELECT * FROM WALMART;

In this image, we have retrieved all the records of the WALMART table by using the SELECT statement.

Now, let’s see the call records of the USA_IKEA table by using the SELECT statement:

SELECT * FROM USA_IKEA;

In this image, we have retrieved all the records of the USA_IKEA table by using the SELECT statement.

SELECT PRODUCT_NAME FROM  WALMART
except 
SELECT PRODUCT_NAME FROM  USA_IKEA
ORDER BY PRODUCT_NAME;

Read: How to Remove User in MariaDB

MariaDB Select First Row

In this section, we will learn how to select the first row in MariaDB by using the syntax and examples.

In MariaDB, to return the first row of the table that matches the SELECT query, we need to add the LIMIT clause to the SELECT statement.

The LIMIT clause is used to control the number of rows by using the query. When we will add LIMIT 1 to the SELECT statement, it will return one row of the table.

The syntax of the LIMIT clause in the SELECT statement is given below:

SYNTAX:

SELECT * FROM TABLE_NAME LIMIT [NUMBERS];

The LIMIT clause accepts only two parameters:

• The number of offsets before returning the row [optional].
• The number of rows to be returned [required].

The sample example of the LIMIT clause in the SELECT statement is given below:

EXAMPLE:

First, let’s see the USA_IKEA table by using the SELECT statement:

SELECT * FROM USA_IKEA;

In this section, we have retrieved all records of the USA_IKEA table by using the SELECT statement.

To retrieve only the first row from the USA_IKEA table by using the LIMIT clause in the below query:

SELECT * FROM USA_IKEA LIMIT 1;

In this section, we have retrieved the first row from the USA_IKEA table by using the LIMIT clause as LIMIT 1 in the SELECT statement. As the output, it provided the output of product_id as 1, product_name as “Skubbs BedSheet and Pillows” with a price of 209.99 in the product_price column.

Read: How to Create View in MariaDB

MariaDB Select Max Row

In this section, we will learn how to use the MariaDB select max row statement with the help of an example.

There is a lot of scenarios when we need the maximum value from the row in the column. We will look for how to select the row with a maximum value on a column using.

The sample example of the MAX ROW function in the SELECT statement is given below:

SELECT 
product_id, product_name
FROM
 usa_ikea
WHERE
product_price = (SELECT 
MAX(product_price)
FROM
 usa_ikea );

In this image, we have retrieved the first row from the product_id, product_price column by using the MAX function.

So as the output, it provided the product_id as 1 and product_name as “Skubbs BedSheet and Pillows” which was having the highest price of 209.99 in the product_price column in the USA_IKEA table.

Read: Replace Function in MariaDB [9 Examples]

MariaDB Select Multiple Columns

In this section, we will learn how to use the MariaDB Select Multiple Columns from the table by using examples.

In MariaDB, the SELECT statement is used to retrieve data from one or more tables. The syntax of the SELECT statement is given below:

SYNTAX:

SELECT EXPRESSION_1,EXPRESSION_2,EXPRESSION_N FROM TABLE_NAME;

The syntax explanation:

• SELECT: it used to select or retreive data from one or more table. At least one table should be inside the FROM clause.
• expression_1,expression_2,expression_n: The expression or columns that we want to select to collect data by using the SELECT statement in the table.
• table_name: The table that we have selected to retreive data from.

The sample example to select multiple columns from the table by using the SELECT statement is given below:

SELECT PRODUCT_NAME,PRODUCT_PRICE FROM USA_IKEA;

In this image, we have selected multiple columns like product_name and product_price from the USA_IKEA table. We have also retrieved all data of both columns by using the SELECT statement in the USA_IKEA table.

Read: How to import CSV files in MariaDB

MariaDB Select Having

In this section, we will learn how to use the MariaDB Select Having clause by using the syntax and examples.

In MariaDB, the HAVING clause is utilized with the GROUP BY clause to limit the group of returned only to those whose condition is true.

The syntax of the HAVING clause in MariaDB is given below:

SYNTAX:

SELECT expression1,expression2,expression_n, 
       aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1,expression2,expression_n
HAVING condition;

The syntax explanation:

• aggregate_funtion: The can be function such a COUNT, SUM, MIN, MAX or AVG functions.
• expression1, expression 2, expression_n: The expression that is not contained in the aggregate_function must be used in the GROUP BY clause.
• WHERE conditions: These conditions are utliized for selecting the records.
• HAVING condition: The condition that are further applied only to get accumulated results to limit the group of resulted rows. Only those groups will be evaluated in the condition whose condition trun out to be TRUE in the resultset.

The sample example of the HAVING clause with the SUM function in the MariaDB is given below:

EXAMPLE:

SELECT product_name,SUM(product_price) AS "Total Price"
FROM usa_ikea
WHERE product_id>=5
GROUP BY product_id
HAVING SUM(product_price) > 100;

In this image, we have used the HAVING clause with the SUM function to return the product_id and total price of all product_price values ( for that product_id) where the product_id is greater than or equal to 5.

The MariaDB Having clause will filter the results so that product_id with the total product_price values greater than 100 will be returned.

Read: MariaDB Timestamp + Examples

MariaDB Select Null

In this section, we will learn how to use the MariaDB Select Null constraint with the help of syntax and examples.

In MariaDB, The IS NULL condition is used to test the NULL value in the statement in the INSERT, DELETE, UPDATE or DELETE statement. The syntax of the IS NULL condition is given below:

SYNTAX:

EXPRESSION IS NULL;

The syntax explanation:

• expression: The value to test whether it is NULL value.

NOTE:

• If the expression is NULL value, the condition evalutes to TRUE.
• If the expression is not NULL value, the condition evalutes to FALSE.

The sample example of the MariaDB Select Null condition is given below:

EXAMPLE:

Let’s look at an example of how to use the IS NULL condition in the SELECT statement:

EXAMPLE: USING SELECT STATEMENT

Let’s look at an example, of how to use the IS NULL condition in the SELECT statement:

SELECT * FROM USA_IKEA WHERE PRODUCT_NAME IS NULL;

In this image, it will return all the records from the USA_IKEA table where the Product_Name contains a NULL value.

EXAMPLE: BY DELETE STATEMENT

Let’s look at how to use the IS NULL condition by using the DELETE statement:

First, let’s see all the records of the USA_IKEA table by using the SELECT statement:

SELECT * FROM USA_IKEA;

DELETE FROM USA_IKEA WHERE PRODUCT_PRICE IS NULL;

As the output produce:

/* Affected rows: 1  Found rows: 0  Warnings: 0  Duration for 1 query: 0.297 sec. */

In this query, the IS NULL value will delete all the records from the USA_IKEA table where the product_price columns contain the NULL value. Let’s see all records of the USA_IKEA table after deletion by using the select statement.

EXAMPLE: SELECT STATEMENT AFTER USING THE DELETE STATEMENT FOR NULL VALUE

SELECT * FROM USA_IKEA;

In this image, we are seeing all records of the USA_IKEA table by using the SELECT statement which is done after the delete statement after the above query.

Read: How to Create Function in MariaDB

MariaDB Select Not Null

In this section, we will learn how to use the MariaDB Select Not Null condition with the help of syntax and examples.

In MariaDB, the IS NOT NULL condition is used to test a NOT NULL value in a SELECT, INSERT, DELETE or UPDATE statement.

The syntax for IS NOT NULL condition in the MariaDB is given below:

SYNTAX:

EXPRESSION IS NOT NULL;

The syntax explanation:

• expression: the value to test whether it is a non- NULL value.

NOTE:

• If the expression is NOT a NULL value, the condition evalutes to be TRUE.
• If the expression is a NULL value, the condition evalutes to be FALSE.

The sample example for the IS NOT NULL value in the MariaDB is given below:

EXAMPLE- BY SELECT STATEMENT

Let’s look at an example of how to use the IS NOT NULL condition with the SELECT statement is given below:

SELECT * FROM USA_IKEA WHERE PRODUCT_NAME IS NOT NULL;

In this image, it will return all records from the USA_IKEA table where the product_name doesn’t contain the NULL value.

Read: MariaDB ERROR 1064

MariaDB Select Duplicates

In MariaDB, the database application stores data in the table in the form of rows and columns. This database can store duplicate records which leads to poor performance of the database in the MariaDB.

However, data duplication can occur due to any reason, and finding the duplicate values in the table is an important task while working with the database in the MariaDB. In this article, we will learn how to find out the duplicate values in the MariaDB.

Lets it understand with the help of an example. First, we will create a table named as USA_WESTONE table by using the following statement:

EXAMPLE:

CREATE TABLE USA_WESTONE(
EARPHONE_ID INT AUTO_INCREMENT PRIMARY KEY,
EARPHONE_NAME VARCHAR(100) NOT NULL,
EARPHONE_PRICE FLOAT);

INSERT INTO USA_WESTONE(EARPHONE_NAME, EARPHONE_PRICE)
VALUES ('ES40 EARPHONES',999.99),
('ES40 EARPHONES',999.99),
('PRO X30',399.99),
('PRO X30',399.99),
('PRO X30',399.99),
('PRO X30',399.99),
('WESTONE AUDIO W80-V3 EARPHONES',999.99),
('Silicon EarTips 12mm -Green',15.99),
('Silicon EarTips 12mm -Green',15.99),
('Silicon EarTips 12mm -Green',15.99),
('EPIC 2-Pin Replacement Cable – Clear – 52 inches',39.99);

SELECT * FROM USA_WESTONE;

The syntax of the MariaDB select duplicate values are given below:

SYNTAX:

SELECT column_name, COUNT(column_name)  
FROM table_name  
GROUP BY column_name  
HAVING COUNT(column_name) > 1;  

The syntax explanation:

• First, we will use the GROUP BY clause for grouping all rows from the desired columns. The desired columns is the column based on which we will check the duplicate records.
• Second, we will use the COUNT() function in the HAVING clause to check the group which has more than one element.

The sample example to the MariaDB select duplicates values from the USA_IKEA table is given below:

SELECT Earphone_Name, Earphone_price, COUNT(Earphone_Name)  
FROM USA_Westone 
GROUP BY Earphone_Name
HAVING COUNT(Earphone_Name) > 1;  

In this image, we will get the output of duplicates values of Earphone_Name and Earphone_price column by using the GROUP BY clause and the COUNT() function in the SELECT statement of the USA_Westone Table.

Read: How to Create Trigger in MariaDB

MariaDB Select Max ID

In this section, we will learn how to use the MariaDB Select Max ID with the help of given examples.

First, let’s have a look at the USA_WESTONE table using the SELECT statement:

SELECT * FROM USA_WESTONE;

The sample example for the MariaDB select max id is given below:

SELECT * FROM USA_WESTONE ORDER BY EARPHONE_ID DESC LIMIT 1;

In this image, we have retrieved the last row from the earphone_id,earphone_name, and earphone_price column by using the MAX function.

So as the output, it provided the earphone_id as 11 and earphone_name as “EPIC 2- PIn Replacement Cable-Clear-52 inches” which was having the price of 39.99 in the earphone_price column in the USA_WESTONE table.

Read: How to Drop Column from MariaDB Table

MariaDB Select Database Version

In this section, we will learn how to use the MariaDB select database version with the help of syntax and examples.

In MariaDB, it returns a string that displays the MariaDB select version. The string uses the UTF8 character set. The syntax of the MariaDB select database version is given below:

VERSION();

The sample example of the MariaDB select database version is given below:

SELECT VERSION();

In this image, we have retrieved the database server version by using the VERSION() function in the SELECT statement. As the output, it provided the version as 10.5.13- MariaDB which is installed on the computer by the user.

MariaDB Select One Row

In this section, we will learn how to use the MariaDB SELECT ONE ROW by using the ORDER BY clause and LIMIT clause which is shown with the help of syntax and example.

In MariaDB, the ORDER BY clause is used to arrange the expression or columns in ascending or descending order. If we don’t use the ASC keyword in the ORDER by clause, it will automatically generate the column in ascending order.

The ORDER BY clause can be also be used to order the activities or a DELETE and UPDATE statement (usually with the LIMIT clause).

The syntax of the MariaDB select one row is given below:

SELECT * FROM TABLE_NAME ORDER BY COLUMN_NAME [ASC| DESC] LIMIT [NUMBER];

Let’s have a look at the USA_WESTONE table by using the SELECT statement:

SELECT * FROM USA_WESTONE;

In this image, we have retrieved all records of the USA_WESTONE table by using the SELECT statement.

The sample example for the MariaDB select one row is given below:

SELECT * FROM USA_WESTONE ORDER BY earphone_price DESC LIMIT 1;

In this image, we have limited all columns by 1 by using the LIMIT 1 in the LIMIT clause and arranged the earphone_price column in descending order by using the ORDER BY clause.

As the result, it produced the output based on the above query due to which it gave the earphone_id column as 2 and earphone_name as ‘ES40 EARPHONES’ and the earphone_price column as 999.99.

Read: How to Add Column in MariaDB

MariaDB Select Not In

In this section, we will learn how to use the MariaDB Select Not IN condition with the help of syntax and examples.

The MariaDB NOT condition is also known as NOT operator and it is used to neutralize a condition in SELECT, INSERT, DELETE or UPDATE statement.

The syntax of the MariaDB Select Not IN condition is given below:

SYNTAX:

NOT CONDITION;

The syntax explanation:

• condition: the condition to negate.

NOTE

The MariaDB NOT condition requires that the opposite of the condition should be met for the records included in the result set.

The sample example of the MariaDB Select Not In condition is given below:

EXAMPLE: Combine with IN condition

First, let’s have a look at the USA_WESTONE table by using the SELECT statement:

SELECT * FROM USA_WESTONE;

The MariaDB NOT condition can be combined with the IN condition. Let’s have a look at how to use the NOT condition with the IN condition:

SELECT * FROM usa_westone
WHERE earphone_name NOT IN ('PRO X30', 'BOAT X-12');

In this image, the MariaDB Select Not IN example has returned all rows from the USA_WESTONE table where the earphone_name column is not ‘PRO X30’ and ‘BOAT X-12’.

Sometimes, it is better to list the values of a table that we don’t want, as opposed to the values we want to return from the result set.

Read: MariaDB Vs SQL Server – Detailed Comparison

MariaDB Select Nth Row

In this section, we will learn how to fetch or select an nth row in the MariaDB. Here’s, how to find the nth row in MariaDB with the help of examples:

Let’s say we may have the following USA_WESTONE table.

SELECT * FROM USA_WESTONE;

Here is the query to find the Nth row in MariaDB. Let’s say we want to return the 5th row from the USA_WESTONE table by using the below query:

SELECT * FROM USA_WESTONE LIMIT 4,1;

In this image, we have used the LIMIT clause to get the Nth row, If we want to get the Nth row then use LIMIT n-1,1 in the query.

Read MariaDB Rename Column

MariaDB Select Random Rows

In this section, we will learn how to select random rows in the MariaDB with the help of syntax and examples.

Sometimes, we want to select random records from the table, for example:

• Selecting random post from the blog and posting it in the side bar.
• Selecting a random quote for displaying the “quote of the day” widget.
• Selecting random pictures in a gallery and use them as a featured image.

In MariaDB, to accomplish this we can use the RAND() function for selecting random rows from the table. The syntax of selecting random rows from the database table is given below:

SYNTAX:

SELECT * FROM TABLE_NAME ORDER BY RAND() LIMIT 1;

The syntax explanation:

• The RAND() function generated random values for each row in the table.
• The ORDER BY clause sorts all rows from the table by the random number generated by the RAND() function.
• The LIMIT clause picks the rows based on the number given by user in the result set randomly.

The sample example to select random records from the table by using the SELECT statement is given below:

SELECT * FROM usa_westone ORDER BY RAND() LIMIT 5; 

NOTE

We may get a different result because of the rand() function for random values in the table.

This technique works on the small table but it gets slower on the big table because MariaDB has to select the entire table to get random ones. The speed of the query also depends on the number of rows in the table. The more rows the table has, the more time it will take to generate the random number of each row.

MariaDB Select Left Join

Here we will explain how to use the MariaDB Outer Left Join with the help of visual illustration, syntax, and examples.

MariaDB Joins are used to retrieve data from multiple tables. The MariaDB Join is performed whenever two or more two tables are joined in a SQL statement.

There are different types of Joins in the MariaDB as given below:

• MariaDB INNER JOIN ( also known as SIMPLE JOIN)
• MariaDB LEFT OUTER JOIN ( also known as LEFT JOIN)
• MariaDB ROGHT OUTER JOIN ( also known as RIGHT JOIN)

LEFT OUTER JOIN

This type of join is called MariaDB LEFT OUTER JOIN. This type of join returns all rows from the left-hand table defined in the ON condition and only those rows from the other table where the joined fields are equal ( join conditions are fulfilled).

The syntax of the MariaDB LEFT OUTER JOIN is given below:

SELECT EXPRESSION FROM TABLE_1
LEFT [OUTER] JOINS TABLE_2
ON TABLE_1.COLUMN=TABLE_2.COLUMN;

In some databases, the LEFT OUTER JOIN keywords are changed to LEFT JOIN.

The MariaDB LEFT OUTER JOIN will return all records from table 1 and only those records from table 2 which intersects.

First, let’s have a look at the USA_WESTONE and WALMART table by using the SELECT statement:

Here, we are going to use USA_WESTONE and WALMART tables.

The sample example of the MariaDB Select Left Join is given below:

SELECT usa_westone.earphone_id, usa_westone.earphone_name, 
walmart.product_id, walmart.product_name
FROM usa_westone
LEFT JOIN walmart
ON usa_westone.EARPHONE_ID = walmart.product_id;

In this image, it would return all rows from the USA_WESTONE table and only those rows from the WALMART table where the joined fields are equal. If an earphone_id column in the USA_WESTONE table doesn’t exist the same type of product_id column in the WALMART table then all fields in the WALMART table will result <NULL>in the result set.

Read: How to Create Table in MariaDB

MariaDB Select from Subquery

The MariaDB tutorial will explain how to use the SUBQUERIES with the help of syntax and examples.

In MariaDB, a subquery is a query within a query. We can create subqueries within our MariaDB statement. The subqueries can reside in the FROM clause, or the WHERE clause, and also in the SELECT clause.

NOTE:

• In MariaDB, a subquery is also called an inside query or INNER SELECT.
• In MariaDB, the main query that contains subquery is also known an OUTER QUERY or OUTER SELECT.

The sample example of the MariaDB Select from subquery is given below:

EXAMPLE- WHERE CLAUSE

Normally, the subquery will be founded in the WHERE clause. It is also known as nested subqueries.

SELECT w.earphone_id, w.earphone_name
FROM usa_westone w
WHERE w.earphone_id IN
(SELECT t.product_id
FROM walmart t
WHERE t.product_price > 100);

The subquery portion of the SELECT statement is given below:

(SELECT t.product_id
FROM walmart t
WHERE t.product_price > 100);

In this query, we will find all product_id values from the WALMART table that have product_price is greater than 100. The subquery is used to filter the main query using IN condition.

MariaDB Select from Select

In this section, we will learn how to use the MariaDB Select from Select with the help of syntax and examples.

In MariaDB, the sub-topic “MariaDB Select from Select ” refers to subqueries. A subquery is a query within a subquery.

EXAMPLE: SELECT CLAUSE

The sample of the SELECT clause in the MariaDB SUBQUERIES is given below:

SELECT p1.earphone_name,
  (SELECT MAX(earphone_price)
   FROM usa_westone p2
   WHERE p1.earphone_id = p2.earphone_id) subquery2
FROM usa_westone p1;

In this example, we have created a SUBQUERY in the SELECT clause as follows:

(SELECT MAX(earphone_price)
   FROM usa_westone p2
   WHERE p1.earphone_id = p2.earphone_id) subquery2

The subquery is aliased to a new name as subquery2. This is a name used to reference this subquery or any of its fields.

The trick to playing a subquery in a SELECT clause is that the subquery must return a single value that’s why the aggregate function like MAX, MIN, SUM, COUNT, or AVG functions are commonly used in the query.

MariaDB Select Most Recent Record

Sometimes we want to retrieve the most recent added field from the database but we don’t know what ID number is in the key field.

Here is some simple MariaDB to let you get a record in MySQL or MariaDB variations. If we have a primary earphone_id field (key field) with an auto_increment number in it. So just replace the TABLE_NAME with the name of your table and PRIMARYIDFIELD with the name of your PRIMARY ID field and it will give us the most recent record of the table.

Try to use it with the ORDER BY clause and the LIMIT clause. Here is a syntax and example below:

SYNTAX FOR MySQL and MariaDB:

SELECT *
 FROM TABLE_NAME
ORDER BY PRIMARYIDFILED DESC
LIMIT 1;

EXAMPLE:

First, let’s have a look at the USA_WESTONE table by using the SELECT statement:

SELECT * FROM USA_WESTONE;

In this query, the last record is recently added to the USA_WESTONE table by using the INSERT INTO table after that all records of the USA_WESTONE table are shown by the SELECT statement.

The last record of the USA_WESTONE table by the earphone_id column as 12, earphone_name as BB6 EARPHONE, and price in the earphone_price column as 25.99.

SELECT * FROM usa_westone ORDER BY earphone_id DESC
LIMIT 1;

MariaDB Select without Table

In this section, we will learn how to use the MariaDB Select without a table with the help of examples.

The FROM clause after the SELECT shows the connection of the table. But if there is no reference to any table then we can use the SELECT statement without the FROM clause.

In other words, we can say that the SELECT statement retrieves the computed rows without any reference to the table. Here is the following example:

EXAMPLE 1:

SELECT CONCAT ("www.mariadb","tips.com") AS DATABASE_SITE_NAME;

In this above query, we have used the CONCAT() function which allows us to concatenate two or more expressions together. It is called as CONCAT_WS() function.

EXAMPLE 2:

SET @VAR="www.mariadbtips.com";

SELECT @var as SITE_NAME;

Read: MariaDB Order By Clause

MariaDB Select By ID

In this section, we will learn how to use the IN condition in the subtopic ” MariaDB Select By ID” with the help of syntax and examples.

In MariaDB, the IN condition is used to reduce the use of multiple OR conditions in the table. It is used in statements like the SELECT, DELETE, INSERT or UPDATE statement.

The syntax of IN condition in the MariaDB is given below:

SYNTAX:

expression IN (value_1,value_2,... or value_n);

or

EXPRESSION IN (SUBQUERY);

The syntax explanation:

• expression: A value to test.
• value_1,value_2, or … value _n: The value to be tested against expression.
• subquery: This is a SELECT statement whose result set will be tested against expression. If any of the expression is matched in the IN condition then it wll be evaluated to be TRUE.

NOTE:

• The MariaDB IN condition is also known as IN operator.
• The MariaDB IN condition will return the records where the expression is in “value_1, value_2 or value_n“.

Let’s look at the example of how to use the IN condition in the MariaDB as given below:

EXAMPLE: with numbers

SELECT * FROM usa_westone WHERE EARPHONE_ID IN (1,5,4,8);

The MariaDB IN condition example will return all rows from the USA_WESTONE table where earphone_id is 1,5,4 or 8 in the table.

MariaDB Select from Dual

In this section, we will learn how to use the DUAL data from the table in the MariaDB with the help of syntax and examples.

In MariaDB, it allows us to specify DUAL as a dummy table_name in a situation where no tables are referenced. It is purely for the convenience of people who requires that all SELECT statement should have the FROM clause or the other clauses.

MariaDB ignores the clauses, it doesn’t require the FROM DUAL statement if no tables are referenced. The FROM DUAL could be used when we only use the SELECT statement for computed values, but it requires a WHERE clause to test that a script correctly handles empty resultsets.

EXAMPLES 1:

SELECT 31+1 AS ADD_CALULATION FROM DUAL;

In this query, we have used the DUAL table as a dummy table to do the arithmetic operation in the query and put the result as the alias name ADD_CALCULATION.

EXAMPLE: WHERE CLAUSE

SELECT 31+1 AS ADDITION FROM DUAL WHERE FALSE;

In this query, we have used the DUAL table as a dummy table for the calculation of arithmetic operation but it will give the resultset as an empty set because the condition of the boolean condition is FALSE.

If the boolean condition turned to be TRUE, it will give a result set in the ADDITION column as 32 for the output.

MariaDB Select Output to File

In this section, we will learn how to use the MariaDB select output to file with the help of examples.

In MariaDB, the SELECT INTO OUTFILE statement saves the resulting rows to a file and enables the use of column and rows terminators into output file format. To terminate the tabs, we use the ‘\t’ and for newlines, we use the ‘\n’.

While creating a new file, that file shouldn’t exist because it can’t be overwritten. The specific location of the MariaDB installation as for the output file path should be given clearly to export the file. The syntax of the MariaDB select output to file is given below:

SYNTAX:

SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]

export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

Let’s show the below query how to export the file for the table_name by an example:

EXAMPLE:

SELECT earphone_id, earphone_name INTO OUTFILE 
'F:\Program Files\MariaDB 10.5\data\airbnb_db\usa_westone.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM usa_westone;

In this query, we have exported the output file of the USA_WESTONE table as the txt extension file which is used for the notepad extension file.

The output message:

/* Affected rows: 12  Found rows: 0  Warnings: 0  Duration for 1 query: 0.313 sec. */

MariaDB Select last 24 hours

In this section, we will learn how to use the MariaDB Now() function for the topic of “MariaDB Select last 24 hours” with the help of syntax and examples.

In MariaDB, the NOW() function is used to return the current date and time. The syntax of NOW() in the MariaDB is given below:

SYNTAX:

NOW()

The syntax explanation:

• There is no parameters or arguments for the NOW() function in the MariaDB.

NOTE:

• If we use the string contest, the MariaDB NOW() function will return the current date as “YYYY-DD-MM HH:MM:SS” format.
• If we use the numberic contest, the NOW() function will return the current date as “YYYYDDMMHHMMSS” format.
• The CURRENT_TIMESTAMP, LOCAL_TIME and LOCALTIMESTAMP function are synomyms for the NOW() function.

Let’s create a new table USA_POSTMATES with help of the following query:

CREATE TABLE USA_POSTMATES(
order_id INT AUTO_INCREMENT PRIMARY KEY,
order_date DATETIME, 
amount INT );

INSERT INTO  USA_POSTMATES(order_date,amount) 
     VALUES ('2022-02-09 01:00:00',200.99),
     ('2022-02-09 02:30:00',350.99),
     ('2022-02-09 04:40:00',410.89),
     ('2022-02-09 12:10:00',600.99),
     ('2022-02-09 15:00:00',300.79),
     ('2022-02-09 18:55:00',450.69),
     ('2022-02-09 21:00:00',1200.00),
     ('2022-02-10 03:00:00',800.99),
     ('2022-02-10 05:30:00',900.99),
     ('2022-02-10 07:20:00',100.99),
     ('2022-02-10 10:10:00',250.99),
     ('2022-02-10 12:05:00',300.89),
     ('2022-02-10 13:30:00',200.79),
     ('2022-02-10 17:20:00',240);

SELECT * FROM usa_postmates;

In this query, we have created a new table USA_POSTMATES with help of the CREATE TABLE statement and added some records in it with the help of the INSERT statement.

As we see in the above query, we retrieve all records of the USA_POSTMATES table with the help of the SELECT statement.

The sample example of the MariaDB Select last 24 hours are given below:

select * from usa_postmates
     where order_date > now() - interval 1 day;

In this query with the help of the NOW() function, it retrieved a row which is done in the last 24 hours as shown above.

MariaDB Select Max Date

In this section, we will learn how to use the Max() function under the topic “MariaDB Select Max Date” with the help of syntax and examples.

In MariaDB, the MAX() function returns the maximum value of an expression. The syntax of the MAX() function in the MariaDB is given below:

SYNTAX:

SELECT MAX(EXPRESSION) FROM TABLE_NAME;

First, let’s have a look at the USA_POSTMATES table by using the SELECT statement as given below:

SELECT * FROM USA_POSTMATES;

In this query, we have retrieved all records from the USA_POSTMATES table by using the SELECT statement.

The sample example of the MAX() in the USA_POSTMATES table is given below:

SELECT MAX(order_date) AS 'MAX DATE OF ORDER' FROM usa_postmates;

In this query, we have retrieved one record from the USA_POSTMATES table by using the MAX() function and kept it as the alias name ‘MAX DATE OF ORDER’ by using the SELECT statement.

MariaDB Select without Header

In this section, the topic “MariaDB Select without Header” readers to retrieve all records or any rows the table without Header or Column Name.

First, start the MariaDB with the -sN option like this:

mysql -sN -u root -p

In this query, the -s keyword is used to skip column names from the table and the -N keyword is used to remove the horizontal and vertical lines from the resultset. After writing the above query, press the ENTER keyboard to enter the password as shown below image.

NOTE

Use all these command lines on the command prompt for better result set of the table.

Next, run the SELECT statement for the USA_POSTMATES table to see skip column names and lines of the table.

SELECT * FROM USA_POSTMATES;

As we will see from the above query in the output that it will remove the column names from the table which was ORDER_DATE and AMOUNT column. Also, it removed the vertical and horizontal lines from the table by using the -sN keyword.

MariaDB Select No Cache

In this section, we will learn how to use the MariaDB select no cache with the help of examples.

In MariaDB, the Server Query Cache (QC) is a well-known feature of the MariaDB Server and it catches SQL statements and corresponding results. If a subquery matches the cache SQL statement then it can’t be reused from the result set.

In the old-time, the cost of CPU was very high and there was one thread. At that time, RAM was limited and disks were slow. The cost was restrictive-up to several years salary to purchase two CPUs.

In those days, the Query cache was a good idea. So getting the data from the disk was a bad idea as it meant to slow the performance of magnetic media. Now, we have faster solid-state disks and more RAM, so the Query cache is less important.

The Query caches have three modes- ON, OFF, and DEMAND. In the OFF mode, nothing will be cached. The same example of the MariaDB Select No Cache statement is given below:

SELECT SQL_NO_CACHE order_date,amount FROM usa_postmates;

As we see from the above query in the below image, we have cached all records of the ORDER_DATE and AMOUNT table by using the SELECT statement in the USA_POSTMATES table. In the background of the query, the performance of the table will decrease that’s it.

Related MariaDB tutorials.

• How to Create Database in MariaDB
• How to create a user in MariaDB
• How To Check MariaDB Version

In this MariaDB tutorial, we have studied the use of the MariaDB Select statements and we have also covered various examples. There are lists of the topic that comes under discussion:

• MariaDB select
• MariaDB select query
• MariaDB select database
• MariaDB select query example
• MariaDB select top 10
• MariaDB select for update example
• MariaDB select for update
• MariaDB select distinct
• MariaDB select count
• MariaDB select all
• MariaDB select as
• MariaDB select all tables
• MariaDB select all columns except one
• MariaDB select all users
• MariaDB select a database
• MariaDB select as Name
• MariaDB select by id
• MariaDB select order by
• MariaDB select count distinct
• MariaDB select duplicates
• MariaDB select database version
• MariaDB select exists
• MariaDB select except
• MariaDB select empty
• MariaDB select first row
• MariaDB select from select
• MariaDB select from subquery
• MariaDB select from dual
• MariaDB select group by
• MariaDB select having
• MariaDB select without header
• MariaDB select last 24 hours
• MariaDB select last row
• MariaDB select last 10 rows
• MariaDB select last_insert_id()
• MariaDB select left join
• MariaDB select max
• MariaDB select multiple columns
• MariaDB select max date
• MariaDB select multiple tables
• MariaDB select most recent record
• MariaDB select max row
• MariaDB select min
• MariaDB select max id
• MariaDB select null
• MariaDB select not null
• MariaDB select no cache
• MariaDB select not in
• MariaDB select nth row
• MariaDB select output to file
• MariaDB select one row
• MariaDB select random rows
• MariaDB select without table