EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial Array in SQL
 

Array in SQL

Priya Pedamkar
Article byPriya Pedamkar

Updated February 27, 2023

Array in SQL

 

 

Introduction to Array in SQL

An array in structured query language (SQL) can be considered as a data structure or data type that lets us define columns of a data table as multidimensional arrays. They are basically an ordered set of elements having all the elements of the same built-in data type arranged in contiguous memory locations. Arrays can be of integer type, enum type or character type, etc.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

How to Create an Array in SQL?

In order to understand array creation in SQL, let us first create a ‘product_details’ table which contains product id, product name, variations, and prices for demonstration purposes. We can use the following SQL statements to perform the task.

product_details

Here, we have successfully created a product_details table with two columns/attributes variations and prices having an array data type. “Variations” have a one-dimensional array data type and “prices” have a multi-dimensional array data type. A one-dimensional array is denoted by the data type of elements in the array followed by “[]” and a multi-dimensional array is denoted by “[][]”.

How to insert Array elements in SQL?

We can insert array elements in an array by mentioning them within curly braces {} with each element separated by commas. Here is an example to illustrate the method for element addition in an array in SQL. Let us insert details into the above mentioned “product_details” table.

The parts in “Green” color are the sections that illustrate array insertion.

element addition

Now we have successfully inserted elements in the table as well as in the mentioned arrays. The data in the product_details table are performing the above-mentioned array insertion looks something like this.

data output

Array Operations in SQL

In this section, we will be discussing some basic array operations like accessing an array element, using array elements in search queries and modifying array elements, etc. So, let us begin.

Accessing an Array element

Accessing an array is as simple as this. Here, we have shown an array called ‘G7 countries’.

Accessing an array element

In order to further illustrate accessing array elements, we will take the help of the “product_details” table.

Examples to Implement Array in SQL

Below are the examples mentioned:

Example #1

Find the first variation of milk with product_id = ‘MD1002’

In this example we are trying to illustrate accessing elements in a one-dimensional array.

variation of milk

Example #2

Find the price of 500gm of milk for “AM1002” product

In this example, we are trying to illustrate accessing elements in a multidimensional array.

Array in SQL6

The order of accessing elements in a multidimensional array is similar to accessing elements of a matrix, but the only difference is here elements start with index = 1 instead of index = 0 as in the case of matrices.

Example #3

Find the product_id for which we can find “double toned” milk variation

This example illustrates the usage of one-dimensional array elements in searching database tables.

Array in SQL7

Example #4

Find the product_id for which the 1 kg of milk costs ‘56’ rupees

This example illustrates the usage of multidimensional array elements in searching database tables

Array in SQL8

The above examples are very helpful if we know the size or the location of attributes in an array. But they become very tedious once the size of the array becomes large. So, what to do to avoid such situations?

We can use parameters like ANY and ALL. The above-mentioned SQL queries can be alternatively written as follows

Array in SQL9

Array in SQL10

We can observe that the results of both the queries are the same. Alternatively, if you do not want to use ANY or ALL, you can use the generate_script() function in pgSQL.

Modifying or Updating Array elements :

Example #5

SQL query to illustrate updating of an entire array

Array in SQL11

We have successfully updated the variations array in the product_details table. Next, we can check if the changes have been made using a SELECT query.

Array in SQL12

Example #6

SQL query to illustrate updating an element of the array

Array in SQL13

We have successfully updated the price array in the product_details table. Next, we can check if the changes have been made using a SELECT query.

Array in SQL14

Conclusion

An array is a data structure that stores elements of the same built-in data-type in a contiguous memory location. Arrays can be one dimensional or multidimensional. They are very useful because they allow for easy access to data elements.

Recommended Articles

This is a guide to Array in SQL. Here we discuss an introduction to Array in SQL, how to create and insert array with examples. You can also go through our other related articles to learn more –

  1. CUBE in SQL
  2. Spark SQL Dataframe
  3. Advantages of NoSQL
  4. JSON in SQL

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW