if I want to duplicate a specific teacher so that they appear twice in this select statement without creating a new a table, how can I do this?

Teacher	Grade	District
Mr. Smith	3rd	West
Mrs. John	4th	South
Mr. Cameron	2nd	North
Mr. Cameron	2nd	North
Kirk Horn	1st	West

And I want it to look like this for Mr. Cameron:

Teacher	Grade	District
Mr. Smith	3rd	West
Mrs. John	4th	South
Mr. Cameron	2nd	North
Mr. Cameron	2nd	North
Mr. Cameron	2nd	North
Mr. Cameron	2nd	North
Kirk Horn	1st	West

What would that select statement look like?

I think for reference I want to do something like this but without the insert: https://dba.stackexchange.com/questions/142414/easiest-way-to-duplicate-rows

CodePudding user response：

If the table is big, and the filter is not selective and backed by an index, then this "trick" avoids a second sequential scan over the table - using PostgreSQL:

SELECT t.*  -- or your list of columns
FROM   test t
     , generate_series(1, CASE WHEN t.teacher = 'Mr. Cameron' THEN 2 ELSE 1 END);

fiddle

It's short syntax for a LATERAL join. See:

• What is the difference between a LATERAL JOIN and a subquery in PostgreSQL? fiddle

If you need more copies, just replace '2' above.

Postgres has a hard time estimating the number of rows to expect with this construct, which may confuse query planning. Stu's variant (doing the same) is slightly more expensive, but easier to estimate for query planning. Syntax needs to be adapted for Postgres:

SELECT teacher, grade, district
FROM   test t
JOIN   LATERAL (VALUES (1),(2)) x(v) ON v = 1 OR teacher = 'Mr. Cameron';

fiddle

CodePudding user response：

You could use UNION ALL

select Teacher,Grade,District
from test
union all
select Teacher,Grade,District
from test 
where Teacher='Mr. Cameron'
order by Teacher;

https://dbfiddle.uk/rBpSL8NW

If you want to force/predict how many duplicate values for Mr. Cameron you will add ,try below query which add only one duplicate value limit 1

(select Teacher,Grade,District
 from test
 )
union all
(
select Teacher,Grade,District
from test 
where Teacher='Mr. Cameron'
limit 1 
  );

https://dbfiddle.uk/uUX5QD3F

CodePudding user response：

You can make use of a rows constructor here with a lateral join.

You've tagged two different RDBMS engines, both support the following, a MySql solution looks like

select Teacher, Grade, District
from t
join lateral (values row(1),row(2))x(v)
where v = 1 or Teacher = 'Mr. Cameron';