Combine INSERT INTO and SELECT for multiple VALUES

Question

I know that the most common way for inserting multiple new rows into a table is:

INSERT INTO fruits (fruit, colorId)
VALUES ('apple', 1),
       ('orange', 2),
       ('strawberry', 3);

I also know that I can insert results obtained from a SELECT, even using custom values:

INSERT INTO fruits (fruit, colorId)
SELECT 'pear', id
FROM colors
WHERE color = 'green';

The thing is that, using any of those options (or maybe a different one, which I do not know), I would like to insert multiple values using the result obtained in such a query. For instance:

SELECT id
FROM colors
WHERE color = 'yellow';

would return a single value 4 (the id for yellow), which I would like to reuse in a multiple insert query such as

INSERT INTO fruits (fruit, colorId)
VALUES ('banana', id),
       ('lemon', id);

(where id should be a 4). Is it possible?

---

EDIT: By the way, I would also like to avoid using subqueries like

INSERT INTO fruits (fruit, colorId)
VALUES ('banana', (SELECT id FROM colors WHERE color = 'yellow')),
       ('lemon',  (SELECT id FROM colors WHERE color = 'yellow'));

Answer 1

BTW, you can do it like below:

DECLARE @Id INT
SET @Id = (SELECT id FROM colors WHERE color = 'yellow')
INSERT INTO fruits (fruit, colorId)
VALUES ('banana', @Id),
   ('lemon',  @Id);

Answer 2

Like so:

INSERT INTO fruits(fruit, colorid)
SELECT names.name, colors.id
FROM colors
CROSS JOIN (
    SELECT 'banana' AS name UNION ALL
    SELECT 'lemon'
) AS names
WHERE colors.color = 'yellow'

In MySQL 8 you could use a table value constructor:

INSERT INTO fruits(fruit, colorid)
SELECT names.column_0, colors.id
FROM colors
CROSS JOIN (VALUES
    ROW('banana'),
    ROW('lemon')
) AS names
WHERE colors.color = 'yellow'