Trouble understanding extra table reasoning in setting up SQL tables, can someone try to explain clearly?

I have the need to set up two dropdown selections, and I am having trouble with understanding the solutions I have.

My Situation
I have a product selection, that will then populate a color selection based on the product. Multiple products will have some of the same colors.

Since it seems an SQL database can't contain array values in columns easily (or maybe it can, but I haven't found a good solution that returned correct results), I searched some alternative solutions. The one I found suggested making three tables, a PRODUCT_ID table, a COLOR_ID table, and a PRODUCT_COLOR table.

I don't understand why that would be easier or less work when adding either more products or colors. Below is the solution that very closely mirrored what I need, but why would you do it that way, instead of setting it up in only two tables and then using something like SELECT color FROM COLORS where PRODUCT_ID = ID where the ID value is the selected value from the product selection, in your query? I have added additional products and colors to illustrate it in a way that seems like it's correct based on the information I have as I add more products/colors. It seems like that solution just adds a third table for the colors and then you would have the same setup in the PRODUCT/COLOR table as the COLORS table in my first idea?

Is there a way I don't know of that allows you to use arrays in a single column so you only have to list one instance of the color and then all product IDs that would be associated with the color? I know this is a different question to my first but I was just curious if it was possible to reduce the amount of lines and columns so that I wouldn't have to manually set up a ton of rows for 100+ products and multiple colors per product or additional tables that feel redundant for no reason. Is the third table a security thing or just to add in availability? I don't need availability but I also saw a similar solution that cross referenced diseases and symptoms with the same idea of three tables, one DISEASES, one SYMPTOMS, and one DISEASES_V_SYMPTOMS using ids

--MY IDEA--

PRODUCT

COLORS

--SOLUTION CODE PROBLEM--
Wouldn't the following produce something similar to this setup?

PRODUCT

COLOR

PRODUCT/COLOR

CREATE TABLE [Product]
(
ID INT IDENTITY(1,1) PRIMARY KEY
,Name NVARCHAR(250)
)

CREATE TABLE [Colour]
(
ID INT IDENTITY(1,1) PRIMARY KEY
,Colour NVARCHAR(250)
)

CREATE TABLE [Product_Colour_Availability] -- Many to Many relationship table
(
ID INT IDENTITY(1,1) PRIMARY KEY
,Product_ID INT
,Colour_ID INT
,Available bit -- 1=available, 0=not available
)

--
-- Insert some data for a single product (T-Shirt) that will be both red and blue.

INSERT INTO [Product] (Name)
VALUES ('T-Shirt')

INSERT INTO [Colour] (Colour)
VALUES ('Red'), ('Blue')

INSERT INTO [Product_Colour_Availability] ([Product_ID], [Colour_ID])
VALUES (1,1,1), (1,2,1)

-- Get availability info about a specific product:

SELECT P.[ID] AS 'Product ID'
,P.[Name] AS 'Product Name'
,C.[Colour] AS 'Product Colour'
,PCA.[Available] AS 'Availability'
FROM [Product_Colour_Availability] PCA
LEFT JOIN [Product] P ON PCA.Product_ID=P.[ID]
LEFT JOIN [Colour] C ON PCA.Colour_ID=C.[ID]
WHERE P.[ID] = 1

Don't attempt to store arrays in column values. You can probably do it with comma-separated lists, but you definitively defeat SQL query performance optimizations by doing that. The people who will put your database into production will curse your name. Loudly. For a long time. (Ask me how I know this sometime

You have entities: things in the physical world that you want to model with rows in your database. Those things have relationships between them.

Your three-table solution includes two entity tables (Product and Colour). And, it looks like it includes a so-called junction table that allows you to construct a many-to-many relationship between the entities. Why is this good? It makes your database a bit smaller and easier to search.

But you have correctly observed that you don't run out of "small", "red" or "t-shirt", you run out of "small red t-shirts". So, your most important entity, the one physical thing you must model correctly if you want to sell stuff, is your Stock-Keeping Unit. SKU

So your question comes down to this: What attributes do you want on your SKU entities? Maybe something like this?

SKU_id  num_in_stock  Size_id  Colour_id  Product_id  description

 1234          7         1         1         1   Small red t-shirt
 1235          0         2         1         1   Medium red t-shirt
 1248          4         3         2         2   Large green hoodie

You can search by colour, size, or product using the _id values. Or, you can just use text descriptions and search for products with slow SQL like this.

description LIKE '%green%'

Your decisions about this are driven by how you want to organize your product presentation and search functions.