SQL | Efficient use of Arrays

Let us assume we have two tables - "founders" and "students":

The table "students" holds the information about students and their respective houses at the Hogwarts School of Witchcraft and Wizardry.

The table "founders" tells us the names of people that created these houses

If we'd like to know the list of students that study under the house created by Godric Gryffindor, we can join the tables "founders" and "students" with a one-to-many relationship. However, we are relinquishing some query performance here as we have to join the two tables in order to arrive at the answer. So why not keep all the data in the same table like below?

Because by using two tables, we are avoiding storage redundancy that comes with storing the founder's name repeatedly in all rows of the "students" table. So using two tables here can cost us in performance while redundant storage in a single table costs us in storage. This is where arrays come to our rescue. Arrays are ordered set of elements that are of a single data type. They can be used when a row's attribute has multiple values - like when a job applicant has multiple skills, an order includes several items etc.

So how do we create a table with arrays compromising neither on storage space nor on query performance? Arrays can be created using square brackets with each element separated by a comma - ["value_1", "value_2", "value_3"].

SELECT "Godric Gryffindor" AS FOUNDER, "Gryffindor" AS HOUSE,
	[
    'Hermione Granger',
    'Ron Weasley',
    'Harry Potter'
	] AS STUDENT
  
UNION ALL  

SELECT "Salazar Slytherin" AS FOUNDER, "Slytherin" AS HOUSE,
	[
    'Draco Malfoy',
    'Vincent Crabbe'	
       `] AS STUDENT

To include multiple columns in the array, we can make use of the STRUCT function.

SELECT "Godric Gryffindor" AS FOUNDER, "Gryffindor" AS HOUSE,
	[
    STRUCT(1 AS ID, 'Hermoine Granger' AS NAME),
    STRUCT(2 AS ID, 'Ron Weasley' AS NAME),
    STRUCT(3 AS ID, 'Harry Potter' AS NAME)
	] AS STUDENT
  
UNION ALL  

SELECT "Salazar Slytherin" AS FOUNDER, "Slytherin" AS HOUSE,
	[
    STRUCT(1 AS ID, 'Draco Malfoy' AS NAME),
    STRUCT(2 AS ID, 'Vincent Crabbe' AS NAME)	
        ] AS STUDENT

Student names are displayed vertically under each house and its founder ensuring great query performance and steering clear of storage redundancy. This array can be unpacked using UNNEST function.

WITH DATA AS
(
SELECT "Godric Gryffindor" AS FOUNDER, "Gryffindor" AS HOUSE,
	[
    STRUCT(1 AS ID, 'Hermione Granger' AS NAME),
    STRUCT(2 AS ID, 'Ron Weasley' AS NAME),
    STRUCT(3 AS ID, 'Harry Potter' AS NAME)
	] AS STUDENT
  
UNION ALL  

SELECT "Salazar Slytherin" AS FOUNDER, "Slytherin" AS HOUSE,
	[
    STRUCT(1 AS ID, 'Draco Malfoy' AS NAME),
    STRUCT(2 AS ID, 'Vincent Crabbe' AS NAME)	
  ] AS STUDENT
)

SELECT NAME, HOUSE, FOUNDER FROM DATA, UNNEST(DATA.STUDENT)