SQL SELECT Statement

The SQL SELECT statement is the most frequently used SQL query or command. It is used to fetch or retrieve all data (columns and rows) or partial (specific columns) data from a database table.

The SQL SELECT command returns matched query data in the form of table (rows and columns).
These tables are called result set or result table.

To rename columns in your query results, use a variant of SQL SELECT AS.

Previously, queries returned results named after table columns.

In select Query, utilise AS to rename a column.

We're renaming FirstName to First and LastName to Last in this scenario.

SELECT FirstName AS First
, LastName AS Last
FROM Person.Person;

Note: The AS is optional.

If you want to use without AS:

Although renaming columns appears to be a useful feature, it causes problems when we wish to return a calculated value.

If you wanted to return all of the LastNames in upper case, for instance. You are a writer.

SELECT UPPER(LastName)
FROM Person.Person

However, when you run the query, you'll notice that no column names are provided. You can utilise AS to ensure that the column is given a name.

SELECT UPPER(LastName) as [LAST NAME]
FROM Person.Person

Method 1:

We can concatenate the values of two columns and apply an alias for that value to make them one.

SELECT FirstName + ' ' + LastName as FullName FROM PersonalDetails

Here the combination of FirstName and LastName is separated by a blank space and given as FullName.

Method 2:

Concatenating the first, middle, and last names together with the + sign.

Another method is to use the plus (+) sign to concatenate fields of different data kinds (varchar, char, int, numeric, etc.). The method's limitation is that if any of the fields being concatenated are NULL, the final string value will be NULL.

I'll demonstrate the SQL procedures for generating a full name for mailing labels using the Person.Person table from the AdventureWorks2012 database. First, consider the classic method of concatenating strings with the + sign (concatenation operator):

SELECT Title, 
 FirstName, 
 MiddleName, 
 LastName,
 Title + ' ' + FirstName + ' ' + MiddleName + ' ' + LastName as SenderName
FROM Person.Person

The MailingName is NULL for any row that has NULL for any one of the name columns. The only rows that have SenderName filled in have a value for all the title, firstname, middlename, and lastname columns. This could be corrected by wrapping ISNULL(column,'') around all the columns in the concatenated field to account for any values having nulls, but that code gets long, messy, and hard to read.

Concat the old way

ISNULL and the plus sign are used in the example syntax below to concatenate values. ISNULL replaces NULL values with the value specified in the second parameter, which is an empty string in this case.

SELECT Title, 
 FirstName, 
 MiddleName, 
 LastName,
 ISNULL(Title,'') + ' ' + ISNULL(FirstName,'') + ' ' + ISNULL(MiddleName,'') 
 + ' ' + ISNULL(LastName,'') as SenderName
FROM Person.Person

The senderName is no longer NULL, as you can see in the example below, because the NULL values were replaced with an empty string. This accomplishes the same result as the CONCAT() function, but with far more code and readability.

Concat with ISNULL

The following code demonstrates how to use the new CONCAT() function with a SELECT statement in SQL Server 2012 and subsequent editions. It substitutes NULL values with an empty VARCHAR string (1). When you need to handle NULL codes and construct a single string in a single column with spaces as a separator, this SQL statement is considerably easier to read and write.

SELECT Title, 
 FirstName, 
 MiddleName, 
 LastName,
 CONCAT(Title,' ',FirstName,' ',MiddleName,' ',LastName) as SenderName
FROM Person.Person

Find special characters

Example 1: Start with our alphareg table to find special characters using our current tables. We check for any data row with one special character of an exclamation point [!] in the first two queries, and any special character of an exclamation point in any data row anywhere in the third query.

SELECT *
FROM alphareg
WHERE Alphabetic LIKE '[!]'
 
SELECT *
FROM alphareg
WHERE Alphabetic LIKE '%[!]%'

Example 2: To retrieve all data rows that contain punctuation characters, beginning with the most frequent comma, period, exclamation point, question mark, semicolon, and colon. Let's look up each of these characters and see what we can find. Any occurrence of these characters will be returned.

SELECT *
FROM alphareg
WHERE Alphabetic LIKE '%[,.!?;;]%'

In the above query, we’re looking for sentences with any of those special characters anywhere.

Example 3: Another example of using Regex to find special characters in a letter immediately following it (without spaces)? We can use special character combinations in the same way that we can use alphabetic or numerical character combinations. In addition, we can see if there is a special punctuation character, followed by a space, any character, and then an alphabetic character.

SELECT *
FROM alphareg
WHERE Alphabetic LIKE '%[,.!?;;][A-Z]%'
 
SELECT *
FROM alphareg
WHERE Alphabetic LIKE '%[,.!?;;][ ]%[A-Z]%'

Because every punctuation mark is followed by either a space or the end of the data row, we have no data that meets our initial query from the aforementioned searches. Our next query yields six results: a special punctuation character, a space, anything, and then an alphabetic character.

This method can be used with all special characters, as well as numbers and alphabetic characters.

Example 4: Let us talk about it today. Before you try out the approach in this blog article, I recommend that you ask other members of your team if they know the answer to this question. The reason I'm asking is that every time I ask this question in an interview, 99 percent of respondents don't know the answer.

Let us first create a small dataset.

Step 1:

CREATE TABLE TestTable (Col1 VARCHAR(100))
GO

Step 2:

INSERT INTO TestTable
VALUES ('[blog.]SQLAuthority.com')
GO

Step 3:

SELECT * FROM TestTable
GO

Now you can see in the result set that there is a record with the terms [blog.]SQLAuthority.com - all we have to do now is create a search condition on the column col1 to look for the column [blog.]. Keep in mind that the goal is to find every row that contains the word [blog.] Let's see how we can replicate this.

Step 4: The natural instinct of many is usually to write the following T-SQL which does not work.

SELECT *
FROM TestTable
WHERE Col1 LIKE ‘%\[blog.]%’
GO

Well, now let us see the answer which works.

SELECT *
FROM TestTable
WHERE Col1 LIKE '%$[blog.]%' ESCAPE '$'
GO

If you run the command above, it will return all of the rows that contain the phrase [blog.] Despite the fact that I have asked numerous people, I have yet to receive a satisfactory response to this question. I doubt many people are familiar with the term ESCAPE. You can substitute any special character for $ in the ESCAPE word, and the script will look for the words right after the given escape words.

For example instead of $ you can also use ! .

SELECT *
FROM TestTable
WHERE Col1 LIKE '%![blog.]%' ESCAPE '!'
GO

The column to be found must be mentioned. Separate the column names with commas. To see the column names, use the Draw function key on the SQL Query window.

For example, to select the DEPTNAME and DEPTNUMB columns, type SELECT DEPTNAME, DEPTNUMB.

Example 1: Type Q.ORG to see the names of the columns in the Q.ORG table. The LIST TABLES command can be used to see a list of tables.

QMF shows a query that picks all of the columns for the table you selected:

SELECT DEPTNUMB, DEPTNAME, MANAGER, DIVISION
 , LOCATION                                                                        
FROM Q.ORG

Leave the query as is, or change it to select a subset of data.

For example, you can delete certain columns or specify criteria for selecting specific rows).

Example 2: If you don't want to see complete rows from your table, simply specify the columns that interest you and divide them with commas. Select the name and birth columns, for instance, if you want to know when your animals were born:

SELECT name, birth FROM pet;

To find out who owns pets, use this query:

SELECT owner FROM pet;

Output:

The query just retrieves the owner column from each record, with some of them appearing many times. To reduce the output, use the keyword DISTINCT to obtain each unique output record just once:

SELECT DISTINCT owner FROM pet;

Output:

A WHERE clause can be used to combine row and column selection. Use this query, for instance, to get birth dates for only dogs and cats:

SELECT name, species, birth FROM pet
WHERE species = 'dog' OR species = 'cat';

Output:

Example 3: While SQL Server is a strong relational database, it can be intimidating at times when it comes to digging up core information about the database system itself.

SQL Server Catalog Views

Catalog views, which are effectively database tables (catalogues in this case) that display system-wide data about the SQL Server Database Engine, are a fundamental topic to grasp in SQL Server.

Querying System Information

A SELECT SQL statement FROM a specific catalogue within the sys. namespace is used to retrieve all catalogue views.

For example, the following statement can be used to view information about all database tables in the system via the sys.tables catalog:

SELECT
  *
FROM
  sys.tables

Example 4: LIKE Statement and Wildcard Character

Before we start into extracting all tables with a specific name, let's have a look at what the LIKE statement and the wildcard (percent) sign do and how they're used together.

LIKE is a query operator that determines whether a specific pattern of characters (usually the values of a specified column) matches a formatted string of characters.

When trying to match the pattern, LIKE is frequently combined with the percent character, which acts as a wildcard. When a percent wildcard character appears in a pattern string, it means that any characters can appear in that spot and the pattern string will still be deemed a match.

If we want to find all books where the title begins with “The” but can contain any characters thereafter, we’d use a statement like so:

SELECT title,
primary_author,
published_date
FROM
books
WHERE
title LIKE 'The%'

Attentive readers will notice that the aforementioned pattern applies not just to titles that begin with "The," but also to titles that begin with the three letters "The." Because percent wildcards match any character, they are useful.

“The ”, adding a space is more appropriate:

SELECT title,
primary_author,
published_date
FROM
books
WHERE
title LIKE 'The %'

Example 5: Selecting Tables Containing a Column Name

With our basic knowledge of both catalog views and the LIKE statement, we are now equipped to lookup all the tables in our system that contain a particular column name:

SELECT
 sys.columns.name AS ColumnName,
 tables.name AS TableName
FROM
 sys.columns
JOIN sys.tables ON
 sys.columns.object_id = tables.object_id
WHERE
 sys.columns.name = 'ColumnName'

We're using a JOIN statement to aggregate information from two catalogues, sys.tables and sys.columns. The object id field connects the two, therefore we JOIN on that field.

Then it's only a question of picking our results' ColumnName and TableName, and then, of course, only checking for records where sys.columns.name equals our ColumnName string.

Example 6: However, this query will only find exact matches of the column name. If we want to find partial matches, we can use LIKE and % wildcard characters instead:

SELECT
 sys.columns.name AS ColumnName,
 tables.name AS TableName
FROM
 sys.columns
JOIN sys.tables ON
 sys.columns.object_id = tables.object_id
WHERE
 sys.columns.name LIKE '%ColumnName%'

Approach 1: Select with from values

Using a find and replace with UNION to retrieve the distinct values of a large list of comma delimited text is the simplest technique to get the distinct values.

SELECT 1
UNION SELECT 1
UNION SELECT 1
UNION SELECT 2
UNION SELECT 5
UNION SELECT 1
UNION SELECT 6

Applied to your long line of comma delimited text.

Find and replace every comma with UNION SELECT and Add a SELECT in front of the statement.

Approach 2: Without from

Let's get one thing out of the way right away: select without from isn't SQL. Stop.

Nonetheless, it operates in a wide range of databases, including those that follow industry standards. There is no contradiction here: the standard permits conforming databases to "offer user options to execute non-conforming SQL expressions." 0 Of course, the seller controls the behaviour of such statements.

The following select statement can thus be implemented as a standard-conforming values without insert:

Instead of a non-conforming select without from:

SELECT CURRENT_DATE

the standard allows the use of values without insert:

VALUES (CURRENT_DATE)

Regrettably, the standalone use of values is still missing from Core SQL. As a result, just three of the six databases evaluated support it. But from the other hand, select without having works on four of them.

You might be wondering why stand-alone values are even useful. Because it is not confined to producing a single row, it is more powerful than select without from.

The following values statement returns today’s and yesterday’s dates (use-case) in two rows—not two columns:

VALUES (CURRENT_DATE)
 , (CURRENT_DATE - INTERVAL '1' DAY)

With select without from, you’d need to use union. That can quickly become bulky.

Conforming Alternatives

SQL Server offers a confirming variant: values is allowed in the from clause, if the from clause assigns column names:

SELECT *
 FROM (VALUES (1,2)
 , (3,4)
 ) t1 (c1, c2)

Use of a dummy table in the from clause is the only other standard-compliant option. Tables are generally included with databases that do not allow select without from (e.g., DUAL in the Oracle database or SYSIBM.DUMMY1 in DB2). Apart from portability, there is no reason not to use them.

Including your own dummy1 table with your software is the simplest method to create a standard-compliant and portable answer.

You may also use a view2 based on the vendor's proprietary dummy table if you don't mind maintaining separate create statements for each target database. If the vendor's dummy table is super-performance-optimized, this may or may not save you from a performance dispute.

You understand how to retrieve data from a table using basic SQL queries. To meet your goals in real-world applications, you'd need to collect data from numerous tables. SQL joins would be required for this. To retrieve fields from many tables, use the join statement.

A join operation in SQL is defined by inserting the names of the tables to be joined in the same FROM clause of a SELECT statement.

Example 1: Assume you want to see a list of all the suppliers, as well as the item numbers and names for the things they supply. The item name is in the INVENTORY LIST table, not the SUPPLIERS table. You may see all of the columns as if they were from a single table by using the general column, ITEM NUMBER.

When two or more tables are being connected and the same column name appears in both, the column name must be qualified by the table name to define which column is being accessed. Because the column name ITEM NUMBER is specified in both tables in this SELECT statement, it must be qualified by the table name. No qualifier is required if the columns have distinct names.

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

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

If you use prompting, you need to type both table names on the FROM tables input line.

Example 2: Let's see the example for the select from two tables:

SELECT orders.order_id, suppliers.name   
FROM suppliers  
INNER JOIN orders  
ON suppliers.supplier_id = orders.supplier_id  
ORDER BY order_id;  

Example 3: Let us take three tables, two tables of customers named customer1 and customer2 and the third table is product table.

Customer1 table

Customer2 table

Product table

Example for select from multiple tables:

SELECT p. p_id, p.cus_id, p.p_name, c1.name1, c2.name2  
FROM product AS p  
LEFT JOIN customer1 AS c1  
ON p.cus_id=c1.cus_id  
LEFT JOIN customer2 AS c2  
ON p.cus_id = c2.cus_id  

Output:

Example 4: To comprehend joins, you must first comprehend the concept of a Cartesian product. This is the product of two sets in mathematics.

A set of two items multiplied by a set of six items, for example, will provide a set of twelve.

A Cartesian product is created by linking every row of one input table to all rows of another table in a database. As a result, the product of a two-row table and a three-row table is a set of six rows.

Example: A Cartesian product of orders and items

Table 1: Orders

Table 2: Items

Result: Each row of orders would multiply by each row of items, and the result would be as below.

The join operator is used in SQL to collect data from many tables. Before the subsequent phases of the query consume the data, the join operator adds or removes rows in the virtual table that SQL server uses to handle data. The following are examples of joins:

A cross join, also known as a Cartesian product, populates the virtual table with all feasible combinations of the rows from the two input tables. The where clause will handle all data filtering for the rows.

The inner join operator constructs a Cartesian product before filtering the results with the ON clause's predicate, deleting any rows from the virtual table that do not satisfy the criteria. It's the most prevalent sort of join.

The LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN operators construct a Cartesian product before filtering the results to identify rows that match in each table. The distinction is that after the initial filter is applied, all rows from one table are maintained and added back to the virtual table. Null values are assigned to attributes that have no matching values.

Inner joins are used to retrieve data from various tables. An inner join starts its logical processing phase as a Cartesian product, which is subsequently filtered to eliminate any rows that don't match the criterion, as previously mentioned.

It's worth noting that INNER JOINS only returns results where both input tables have a match.

SELECT o.orderid, o.amount, i.description
FROM orders o
INNER JOIN items i
ON o.itemid = i.itemid;

Some key elements to note from the query above:

The FROM, SELECT, and ON commands all employ aliases. The orders table is aliased as o and is referenced in both the ON and SELECT statements.

Only JOIN could be used to express the essential term INNER JOIN. Both terms denote an inner connection.

Because we're utilising an INNER JOIN, the only rows that are present in BOTH tables will be returned.

Example of Inner Join

Consider the tables below.

Table Orders

Table Items

In the tables above, if we needed to retrieve orderid, order quantity, item price, and itemdesc for all orders that have an item, the query would be as follows.

SELECT o.orderid, o.qty, i.itemprice, i.itemdesc
FROM orders o
INNER JOIN items i
on o.itemid = i.itemid

The result set would be as below.

Because itemid 3 in the orders table is not present in the items table, orderid 4 did not appear in the result set, as seen above. Orderid 4 was filtered out of the result set because INNER JOIN only returns rows that exist in both tables.

You've already learnt how to utilise inner joins to join rows from different tables. SQL Server created the results of an inner join query by filtering out rows that did not fulfil the ON clause predicate's constraints. As a result, only the rows that matched across both tables were shown. You can opt to display all the rows from one table, as well as those that match from the second table, using an outer join.

Here are some important notes about outer joins:

Outer joins return all rows from one table as well as matching rows from the second.

The results from the second table are presented as NULL in circumstances where the join cannot find matching records from the second table. Unlike inner joins, the sequence in which tables are listed and joined in the FROM clause does significant, as it determines whether your join is LEFT or RIGHT.

All rows from the first table (a) will be returned, as well as only matched rows from the second table (b).

FROM a LEFT OUTER JOIN b
on a.id = b.id

The following will return all rows from the second table (b) and only matching rows from the first table (a).

FROM a RIGHT OUTER JOIN b
on a.id = b.id

Example of Outer Join

Consider the tables of orders and items used in the inner join example above.

Assume we need to construct a query to get all orders' orderid, order quantity, item price, and itemdesc. The order information should be returned, however the item information should be shown as NULL if the order does not have an itemid in the items table.

In this case, an LEFT (outer) JOIN is preferable since it returns all data from the first table as well as only matched rows from the second.

The query would be as follows.

SELECT o.orderid, o.qty, i.itemprice, i.itemdesc
FROM orders o
LEFT JOIN items i
on o.itemid = i.itemid

Result:

The Cartesian product is created using cross join queries, which you learnt about previously in this course.

The CROSS JOIN operator is used to generate a Cartesian product explicitly. This will provide a result set with all conceivable input row permutations.

When constructing CROSS JOIN queries, keep in mind that no row matching is done, hence there is no need for an ON clause.

Consider the example below.

Table B

SELECT *
FROM A
CROSS JOIN B

Result:

So far, you've learned about joins that involve numerous tables being joined together. There are times when you'll need to compare and get data from the same table.

In a human resources application, an Employees table, for example, can include information about each employee's supervisor in the employee's own row. Every supervisor is identified as an employee as well. A possible scenario is depicted in the table below.

You can use the table twice in your query to get employee information and match it to the corresponding supervisor, connecting it to itself for query reasons.

To accomplish tasks like this, you should consider the following guidelines:

In the FROM clause, create two instances of the same table and join them as needed with inner or outer joins.

To create two distinct aliases for the same table, use table aliases. There must be an alias for at least one of these.

To construct a filter utilising separate columns from the same table, use the ON clause.

SELECT e.empid ,e.empname AS empname, s.empname AS supervisorname
FROM HR.Employees AS e
JOIN HR.Employees AS s
ON e.supervisorid=s.empid

This would return each employee with their respective supervisor.

There is no built-in statement in MySQL for selecting random rows from a table. The RAND() function is used to do this. Using the (*), get random rows from all columns of a table. Retrieve random rows just from the table's specified column. Only the column name, table name, and RAND() must be entered.

• For each row in the table, the function RAND() generates a random value.
• The RAND() method generates a random number, which the ORDER BY clause uses to rank all rows in the table.
• The LIMITclause selects the first row in the sorted result set.

To get random rows from the table, use one of the syntaxes shown below. Many developers use it to retrieve random questions from databases for exam websites.

Example 1: It is sometimes necessary to retrieve a random record from the table. For example, if you have a table of quotes and want to display a random quote on the GUI, you'll need to construct a SQL query to retrieve a random record from the table of quotes. In this article, we'll learn how to use the RAND function to select a random record from a table.

Real World examples:

1. Online exams, where you want to display a random question.

2. For an e-commerce website to display random featured items on home page.

3. Display a random featured image on a website

Selecting random rows from table in MySQL

Syntax 1:

The number of random rows you wish to fetch is specified by N. For instance: Use the numeral 1 in place of N if you just want to get one random row.

SELECT column_name FROM table_name
ORDER BY RAND()  
LIMIT N;

Syntax 2: Select All Column Random Rows.

SELECT * 
FROM tablename
ORDER BY RAND();

The above syntax select the random from all the columns of a table.

Syntax 3: Retrieve Random Rows From Selected Columns in Table.

SELECT column_name 
FROM tablename
ORDER BY RAND();

The formula above selects just random rows from the specified columns. You only need to input the column names from which you want to get random rows and the table name from which you want to get random rows from the given columns.

Parameter Descriptions

Example: When we forget the passwords, the system asks the random security questions to verify the identity. Lets take the same example. Here we have a table, where we have security questions stored and we are fetching a random question from the table.

Table: Questions

The following SQL statement will fetch 1 random record from the table Questions

SELECT QUESTION
FROM Questions
ORDER BY RAND()
LIMIT 1;

Result:

Note: Because the question is chosen at random, the result may differ for you. You might obtain a different result if you execute this command again.

Select random row from table in PostgreSQL

To do the same thing in PostgreSQL, we use RANDOM() function instead of RAND().

SELECT column_name 
FROM table_name
ORDER BY RANDOM()
LIMIT 1;

Select random row from table in Microsoft SQL Server

SELECT TOP 1 column_name 
FROM table_name
ORDER BY NEWID();

Select random row from table in Oracle

SELECT column_name
FROM
( SELECT column_name 
  FROM table_name
  ORDER BY dbms_random.value 
)
WHERE ROWNUM = 1;

Example 2: The following query selects a random row from a database table:

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

If you want to select N random records from a database table, you need to change the LIMIT clause as follows:

SELECT * FROM table_name
ORDER BY RAND()
LIMIT N;

See the following customers table from the sample database.

The following example selects five random customers from the customers table:

SELECT 
 customerNumber, 
 customerName
FROM
 customers
ORDER BY RAND()
LIMIT 5;

Example 3: The following query generates a random number based on the primary key column:

SELECT 
ROUND(RAND() * ( SELECT MAX(id) FROM  table_name)) AS id;

We can join the table with the result set returned by the above query as follows:

SELECT t.*
FROM table_name AS t
INNER JOIN
 (SELECT ROUND(
  RAND() * 
  (SELECT MAX(id) FROM table_NAME )) AS id
  ) AS x
WHERE
  t.id >= x.id
LIMIT 1;

If you exceed the limit, the query will only give you consecutive rows that start from the randomly picked row, so you'll have to run it numerous times to acquire more than one random row using this strategy.

The following query returns a random customer from the customers table.

SELECT 
 t.customerNumber, t.customerName
FROM
 customers AS t
 JOIN
 (SELECT 
  ROUND(RAND() * (SELECT 
  MAX(customerNumber)
  FROM
  customers)) AS customerNumber
    ) AS x
WHERE
  t.customerNumber >= x.customerNumber
LIMIT 1;

Example 4: MySQL select random records using variables

In case, the table has id column with the values that fall within a range 1..N and there is no gap in the range, you can use the following technique:

• First, select random numbers in the range 1..N.
• Second, pick the records based on the random numbers.

The following statement helps you accomplish this:

SELECT 
    table. *
FROM
 (SELECT 
 ROUND(RAND() * (SELECT 
 MAX(id)
 FROM
 table)) random_num,
 @num:=@num + 1
 FROM
 
 (SELECT @num:=0) AS a, table
 LIMIT N) AS b,
 table AS t
WHERE
 b.random_num = t.id;

Note that the user-defined variables are connection-specific. It means that this technique cannot be used with the connection pooling. In addition, the primary key must be integer type and its values must be in the sequence without gaps.

We will learn how to write a SQL query with a space in the column name in this tutorial. In the naming convention of the database object's name and the table's column name, blank spaces are prohibited. The query and application code must be written differently if you want to include a blank space in the object or column name. When writing dynamic SQL queries, you must be accurate and careful.

In SQL Server, we can use square brackets or parenthesis to specify the column name. Let's look at few examples to better comprehend the notion.

Use the back tick sign with the column name to choose a column name with spaces. (' ') is the symbol. The back tick appears below the tilde operator (~) on the keyboard.

Example 1: Tidle Operator(~)

Step 1: Firstly, create a table:

CREATE table SpaceColumn(`Student Name` varchar(100));

Step 2: Inserting records

INSERT into SpaceColumn values('John');
INSERT into SpaceColumn values('Bob');

Step 3: The syntax to get column name with space is as follows :

SELECT `column_name` from yourTableName;

Step 4: Now I will apply the above syntax to get the result for my column. The query is as follows:

SELECT `Student Name` from SpaceColumn;

Output:

Example 2: Suppose we want to create a table named Employee table. We create the following query to create the table:

use DemoDatabase
go
create table Employee Table
(
Employee_ID int,
first_name varchar(50),
first_name varchar(50),
Dept_id int,
grade char(5)
)

When we execute the query, we will receive the following error:

To fix this error, we can specify the table name in the Square bracket.

use DemoDatabase
go
create table [Employee Table]
(
Employee_ID int,
first_name varchar(50),
last_name varchar(50),
Dept_id int,
grade char(5)
)

Alternatively, you can specify the table name in between double-quotes (“).

use DemoDatabase
go
create table "Employee Table"
(
Employee_ID int,
first_name varchar(50),
last_name varchar(50),
Dept_id int,
grade char(5)
)

Space in the column’s name

Let's say we want to make a tblCountries table. Country code and country name are the two columns in the table. The create table statement is as follows.

create table tblCountries
(
Country code varchar(15),
Country Name varchar(15)
)

To fix this error, we must specify the column name in the Square bracket.

use DemoDatabase
go
create table tblCountries
(
[Country code] varchar(15),
[Country Name] varchar(15)
)

Example 3: You can fill the columns with space in the name by using square brackets. Let's say we want to retrieve the tblCountries table's country code and country name columns.

use DemoDatabase
go
select  country code, country name
from tblCountries

The SELECT statement returns an error:

We must specify the column in the square bracket or double quote. The query should be written as follows.

use DemoDatabase
go
/*Query with square bracket*/
select  [country code], [country name]
from tblCountries
Go
/*Query with double quotes*/
select  "country code", "country name"
from tblCountries
Go