If the car is stationary, then you're rotating the tires about a vertical axis, so you're rotating the contact patch of the tire relative to the road, and there is a lot of friction coming from those two surfaces sliding across each other. (In the center of the contact patch, there's no friction because there's no relative motion, but on the edges of the contact patch, there's a lot of friction because the rotation of the tire moves those surfaces directly across each other.)
If the car is driving in a straight line, then there's no friction because there's no relative motion between the contact patch and the road (no slippage). The tire is rotating about a perfectly horizontal axis, so the part of the tire just in front of the contact patch is about to press down onto the road and the part just behind is lifting off, but no surfaces are sliding across each other. (There is still energy loss, but it comes from deformation of the tire and the road, not from friction between surfaces. See [Rolling resistance].1
Now for the complicated part. If the car is moving forward at a moderate speed and the wheels are rolling as usual, but you're also turning the steering wheel so the car is following a curved path, then in order to think about the friction we have to think about the axis of the NET rotation of the tire. The forward motion of the car gives it a lot of rotation about a horizontal axis. If we use the right-hand rule then this angular velocity vector points to the left. But the steering motion is also rotating the tire, much more slowly, about a vertical axis. For example, if you're turning the car to the left, then this gives a small angular velocity vector pointing up. To find the NET angular velocity, we should add together those two angular velocity vectors, which gives a vector pointing mostly to the left, but slightly up. This is the axis of the NET rotation of the tire at an instant in time.
Since this axis is much closer to horizontal than it is to vertical, the tire is mostly rolling forward and the difference in speed between the left and right sides of the contact patch (which causes the relative motion between surfaces, hence friction) is much less than it is when the car is stationary.